VBdotnet2005
asked on
SQL - select
I want to list all productname for each column. A list of productname is on table2. There are about 50 of them.
Instead of using case (50 times or more) like below, is there a better way of query it?
select
tb1.producttype,
case productname when "productname1" then tbl2.shipped * quantity as productname1 else 0 end) productname1,
case productname when "productname2" then tbl2.shipped * quantity as productname1 else 0 end) productname2,
case productname when "productname3" then tbl2.shipped * quantity as productname1 else 0 end) productname3,
...
case productname = "productname50" then tbl2.shipped * quantity as productname1 else 0 end) productname50,
tb1.shippedto,
tbl1.Customername
from table1 tb1
inner join table2 tbl2
on tb1.id = tbl2.id
Instead of using case (50 times or more) like below, is there a better way of query it?
select
tb1.producttype,
case productname when "productname1" then tbl2.shipped * quantity as productname1 else 0 end) productname1,
case productname when "productname2" then tbl2.shipped * quantity as productname1 else 0 end) productname2,
case productname when "productname3" then tbl2.shipped * quantity as productname1 else 0 end) productname3,
...
case productname = "productname50" then tbl2.shipped * quantity as productname1 else 0 end) productname50,
tb1.shippedto,
tbl1.Customername
from table1 tb1
inner join table2 tbl2
on tb1.id = tbl2.id
when pivoting data using SQL, the column names (bold) are "hard coded"
select
tb1.producttype,
case productname when 'ABC123' then tbl2.shipped * quantity else 0 end) ABC123,
case productname when '123XYZ' then tbl2.shipped * quantity else 0 end) 123XYZ,
case productname when 'BLAH17' then tbl2.shipped * quantity else 0 end) BLAH17,
...
case productname = 'ETCETC50' then tbl2.shipped * quantity else 0 end) ETCETC50,
But you could concatenate them into a query from variables using C#
Otherwise you would need "dynamic sql"
This comment is true with the PIVOT operator and/or case expression approaches.
select
tb1.producttype,
case productname when 'ABC123' then tbl2.shipped * quantity else 0 end) ABC123,
case productname when '123XYZ' then tbl2.shipped * quantity else 0 end) 123XYZ,
case productname when 'BLAH17' then tbl2.shipped * quantity else 0 end) BLAH17,
...
case productname = 'ETCETC50' then tbl2.shipped * quantity else 0 end) ETCETC50,
But you could concatenate them into a query from variables using C#
Otherwise you would need "dynamic sql"
This comment is true with the PIVOT operator and/or case expression approaches.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BUT (in my opinion)
the case expression approach can do stuff rather easily that is very hard to achieve using PIVOT
The very best way of proceeding is for you to supply some "sample data" and an "expected result"
nb:
sample might only be 4 columns, not 50, and just a couple of rows.
a good example of this is shown at the link provided above.