[SQL Server] Retrieve Multiple Rows as a Single Record with T-SQL

Sharing buttons:

in this example we have two tables a

first one containing a list of products

with an ID as primary key and a second

one representing various parameters that

can be assigned to each product each row

of the parameter of stable is a single

parameter and that means that if we won

the whole set of parameters a product

can hail

we must squirrel the entire parameters

record set joining the products ID field

with the idea of products table we will

request all the fields from products

table plus the fields label and

parameter from the parameter of stable

in our query this later table was called

p1 using an inner join the query will

discard products that don't have at

least a record on parameter stable

if we execute this query we'll get three

records each one with the full product

information and difference label in

parameter fields that can be fine but it

is redundant about products then there

are cases in which we want a single row

that exposes all possible information

and not a multi records plate parameter

stable as a primary key on Product ID

label ID and we can exploit it for our

purposes let's complete our join add in

a condition in which we request only

records with a label ID that equals to

one will obtain a record with products

information plus the parameters

identified by first label ID occurrence

we can write multiple inner join on

parameters table calling it with

distinct names each time and working on

different label ID like this another

query show us this way we can retrieve

products information along with the

parameters on a single row to include

products without parameters

correspondence it is sufficient to

substitute each inner join with a left