CODEWITHSUNDEEP
sqlsql-server
user441521
user441521

Reputation: 6998

Transposing records to column in sql server

The title might not even be the right thing I'm looking for but looking for some other ideas on how to query things.

I have the following tables:

items (itemID, upc, name)
levels (levelID, desc)
levelGrp (levelGrpID, levelID, desc)
levelGrp_Intersection (itemID, levelID, levelGrpID)

levels table would have just 3 records:

  { 1, Department }
, { 2, Category }
, { 3, Sub-Category }

levelGrp table defines descriptions that has the right level:

  { 545, 1, Beverages }
, { 546, 2, Alcohol }
, { 547, 3, Beer }

So you can see the correlation. One department is called Beverages one Category is called Alcohol and one sub-category is called Beer.

Now the levelGrp_Intersection table will add an item for each so you'd have:

{ 100, 1, 545 }
{ 100, 2, 546 }
{ 100, 3, 547 }

So this says:

item 100 has its Department as Beverages
item 100 has its Category as Alcohol
item 100 has its Sub-Category as Beer

The question now is querying this. If I want to see what the Department, Category, and Sub-Category are for each item what's the best way.

I know I can do subselects in the select statement to get this information but subselects are generally considered bad (from my understanding) so what are my other options?

Ideally I'd want just 1 row for each item and we'd be creating columns for Department, Category, Sub-Category which are really records in a table. It's like we're taking these records and converting them to columns. Not that levelGrp has about 700+ records so pivoting on that isn't really an option and no dynamic sql.

Any ideas?

Upvotes: 1

Views: 40

Answers (1)

SqlZim
SqlZim

Reputation: 38043

Using conditional aggregation with known level names:

select
    lgi.item
  , Department  = max(case when levelid = 1 then lg.[desc] end)
  , Category    = max(case when levelid = 2 then lg.[desc] end)
  , Subcategory = max(case when levelid = 3 then lg.[desc] end)
from levelGrp_Intersection lgi
  inner join levelGrp lg
    on lg.levelGrpId = lgi.levelGrpID
group by lgi.item

Upvotes: 1

Related Questions