srobinson 596 (11/12/2012)

---

So I have been reading up on pivot tables because I thought that was the direction I needed to go in. However - I am not aggregating any data. So let me first present my scenario - after that any insight would be appreciated.

I am creating a report for items that have an id as well as a material description. An item can exist multiple times with different types of material. Also - an item can have no more than 5 materials. For example:

item ID Material
337139 Ring-Band
337139 Ring-Artisan
337139 GOLD
337156 Acc-Tokens
337174 Ring-Gemstone
337174 Ring-Artisan
337174 SILVER
337183 Ring-Band
337183 MIXED METAL
337184 Earrings-Hoop
337184 MIXED METAL
337194 Earrings-Dangle
337194 SILVER

What I am looking to do is invert the data - so an item would only show up once followed by up to 5 materials.

item id Material 1 Material 2 Material 3 Material 4 Material 5
337139 Ring-Band Ring-Band Ring-Artisan
337156 Acc-Tokens
337174 Ring-Gemstone Ring-Artisan SILVER


Is there a way to transpose this then? Any help would be appreciated.

I have created some insert statements for the data:
create table materials (item_id int, materials varchar(35))
insert into materials values (337139,'Ring-Band');
insert into materials values (337139,'Ring-Artisan') ;
insert into materials values (337139,'GOLD');
insert into materials values (337156,'Acc-Tokens');
insert into materials values (337174,'Ring-Gemstone');
insert into materials values (337174,'Ring-Artisan');
insert into materials values (337174,'SILVER');
insert into materials values (337183,'Ring-Band');
insert into materials values (337183,'MIXED METAL');
insert into materials values (337184,'Earrings-Hoop');
insert into materials values (337184,'MIXED METAL');
insert into materials values (337194,'Earrings-Dangle');
insert into materials values (337194,'SILVER');

WITH
cteEnumerateMaterials AS
(
 SELECT item_id, materials,
        Material# = ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY materials)
   FROM materials
)
 SELECT [Item ID] = item_id,
        [Material 1] = MAX(CASE WHEN Material# = 1 THEN materials ELSE '' END),
        [Material 2] = MAX(CASE WHEN Material# = 2 THEN materials ELSE '' END),
        [Material 3] = MAX(CASE WHEN Material# = 3 THEN materials ELSE '' END),
        [Material 4] = MAX(CASE WHEN Material# = 4 THEN materials ELSE '' END),
        [Material 5] = MAX(CASE WHEN Material# = 5 THEN materials ELSE '' END)
   FROM cteEnumerateMaterials
  GROUP BY item_id
;