Need to transpose some data - and not sure of the best way to do so - pivot table perhaps?

Question

Need to transpose some data - and not sure of the best way to do so - pivot table perhaps?

srobinson 596

Mr or Mrs. 500

Points: 539
More actions
November 12, 2012 at 2:28 pm

#264626

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
337139Ring-Band
337139Ring-Artisan
337139GOLD
337156Acc-Tokens
337174Ring-Gemstone
337174Ring-Artisan
337174SILVER
337183Ring-Band
337183MIXED METAL
337184Earrings-Hoop
337184MIXED METAL
337194Earrings-Dangle
337194SILVER
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');

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

One thing i do when i need something like these requirements is create a consistent uniquifier using something like ROW_NUMBER() and have the base of my pivot something like this:

SELECT ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY (SELECT NULL)) as uniqifier, item_id, materials

FROM materials

Then i just take the MAX() for my row number for my pivot table "Aggregation".

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

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
337139Ring-Band
337139Ring-Artisan
337139GOLD
337156Acc-Tokens
337174Ring-Gemstone
337174Ring-Artisan
337174SILVER
337183Ring-Band
337183MIXED METAL
337184Earrings-Hoop
337184MIXED METAL
337194Earrings-Dangle
337194SILVER
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');

Nicely done. I absolutely love it when someone takes the time to help me help them with readily consumable test data.

The following code will do what you ask. Keep in mind that an aggregate can be an aggregate of just one element. 😉 It employs what CapnHector was talking about.

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

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

srobinson 596 Mr or Mrs. 500 Points: 539 More actions · Answer 3

Thanks for the responses guys - both worked well! You guys rock!