Transposing records to column in sql server

Question

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?

no pivot, no dynamic = no donut. Can you pivot it on the caller application? And what sql server version? — Rubens Farias
– Rubens Farias, Commented Mar 7, 2017 at 16:22
2012. No pivot on the caller app. The data that returns from a proc that does this simply passes the data along as is no exception. Maybe a pivot can be done? I mean we are hard coding the levels (1, 2, 3) when looking but that joins to levelGrp which just has too many records (700+) and we don't want to hard code those values, but maybe the pivoting happens on the 1, 2, 3 values and we get the description from the levelGrp table? I just can't think of how that would work. — user441521
– user441521, Commented Mar 7, 2017 at 16:27

SqlZim · Accepted Answer · 2017-03-07 16:28:40Z

1

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

answered Mar 7, 2017 at 16:28

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user441521 Over a year ago

Worked like a charm! Thank you!

Collectives™ on Stack Overflow

Transposing records to column in sql server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related