Pivoting name value pairs

Question

Post reply

Pivoting name value pairs

pYak

Ten Centuries

Points: 1027
More actions
April 17, 2010 at 6:26 pm

#146584

Hi
I would like to flatten a relational representation of a transaction to 1 row.
However because the name-value pairs are derived from a number of forms
the name attribute can vary from with each transaction.
Is there a way to pivot this data without using dynamic sql?
Setup the destination table
CREATE TABLE #NameValue(
[id][int] IDENTITY(1,1) NOT NULL,
[tran_id][int] NOT NULL,
[name1][nvarchar](50) NULL,
[value1][nvarchar](50) NULL,
[name2][nvarchar](50) NULL,
[value2][nvarchar](50) NULL,
[name3][nvarchar](50) NULL,
[value3][nvarchar](50) NULL
);
Setup the transaction table
CREATE TABLE #Transaction(
[id][int] IDENTITY(1,1) NOT NULL,
[tran_id][int] NOT NULL,
[row_id][int] NOT NULL,
[name][nvarchar](50) NULL,
[value][nvarchar](50) NULL,
);
Populate the transaction table
INSERT INTO #Transaction SELECT 10, 1, 'Postcode', '12345';
INSERT INTO #Transaction SELECT 10, 2, 'Gender', 'Male';
INSERT INTO #Transaction SELECT 10, 3, 'Location', 'Keys';
INSERT INTO #Transaction SELECT 20, 1, 'Car Make', 'Ford';
INSERT INTO #Transaction SELECT 20, 2, 'Model', 'Focus';
INSERT INTO #Transaction SELECT 30, 1, 'Postcode', '54321';
INSERT INTO #Transaction SELECT 30, 2, 'Gender', 'Female';
INSERT INTO #Transaction SELECT 30, 3, 'Location', 'Biscayne';
INSERT INTO #Transaction SELECT 40, 1, 'Hospital', 'Mercy';
INSERT INTO #Transaction SELECT 40, 2, 'Clinic', 'Heart';
INSERT INTO #Transaction SELECT 40, 3, 'Prognosis', 'Good';
Here is the desired result
--idtran_idname1value1name2value2name3value3
--110Postcode12345GenderMaleLocationKeys
--220Car MakeFordModelFocusNULLNULL
--330Postcode54321GenderFemaleLocationBiscayne
--440HospitalMercyClinicHeartPrognosisGood
Thanks

Viewing 15 posts - 1 through 15 (of 32 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the CrossTab and DynamicCrossTab articles referenced in my signature.

I think those articles will answer any questions you have regarding this pivoting issue...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pYak Ten Centuries Points: 1027 More actions · Answer 2

Thanks Lutz

I'll have a look at the links you provided.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Heh... you know what you get from me when you post tables, readily consumable data, and a nice list of what the results should be?........ A tested, coded answer! 😛 Thanks for taking the time to "do it right". 🙂

INSERT INTO #NameValue

([tran_id],[name1],[value1],[name2],[value2],[name3],[value3])

SELECT [tran_id],

MAX(CASE WHEN [row_id] = 1 THEN [name] END) AS [name1],

MAX(CASE WHEN [row_id] = 1 THEN [value] END) AS [value1],

MAX(CASE WHEN [row_id] = 2 THEN [name] END) AS [name2],

MAX(CASE WHEN [row_id] = 2 THEN [value] END) AS [value2],

MAX(CASE WHEN [row_id] = 3 THEN [name] END) AS [name3],

MAX(CASE WHEN [row_id] = 3 THEN [value] END) AS [value3]

FROM #Transaction

GROUP BY [tran_id]

ORDER BY [tran_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)

pYak Ten Centuries Points: 1027 More actions · Answer 4

pYak

Ten Centuries

Points: 1027

April 18, 2010 at 11:45 pm

#1153594

Thanks Jeff. That's great!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Absolutely my pleasure. Thanks for the feedback.

You still might want to read the articles that Lutz pointed out, though. I know the guy that wrote them and he put some good stuff in them. 😉

--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)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Hey hi, i know Jeff has given a code for your requirement, which will be 100% fast and reliable..

I just tried out some other way of coding your requirement and i had come up with this.. Mine will be messy, but it is producing the exact same execution plan as the one from Jeff.

SELECT

[tran_id],

SUBSTRING([1],1,(CHARINDEX(';',[1])-1)) name1,

SUBSTRING([1],(CHARINDEX(';',[1])+1),DATALENGTH([1])-(CHARINDEX(';',[1]))) value1,

SUBSTRING([2],1,(CHARINDEX(';',[2])-1)) name2,

SUBSTRING([2],(CHARINDEX(';',[2])+1),DATALENGTH([2])-(CHARINDEX(';',[2]))) value2,

SUBSTRING([3],1,(CHARINDEX(';',[3])-1)) name3,

SUBSTRING([3],(CHARINDEX(';',[3])+1),DATALENGTH([3])-(CHARINDEX(';',[3]))) value3

FROM

(SELECT [tran_id], [row_id] , [NAME]+';'+[value] name_value FROM #Transaction) FOR_PIVOT

PIVOT

(MAX(name_value) FOR [row_id] IN ([1], [2], [3])) PIVOT_CONTROL

Tell us back if this worked for you as well 🙂 Jeff's will be more readable though :w00t:

Cheers!!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

As you said no dynamic sql, i had come up with this approach.. one dis-advantage i am thinking of in this one will be the ; (semi-colon)character i am using to be delimiter character for the concatenated strings.. if your real input column values are to have that semi-colon, then you might want to change that delimiter character (in this case , the semi-colon) to something else that your source data in the columns wont get 🙂

Cheers!!!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

Jeff Moden (4/19/2010)
You still might want to read the articles that Lutz pointed out, though. I know the guy that wrote them and he put some good stuff in them. 😉

Yes he has, Jeff.. LOL.. 😀

karthik M One Orange Chip Points: 29139 More actions · Answer 9

but it is producing the exact same execution plan as the one from Jeff.

Yes. I tested both the query plan...i didn't see any difference.

karthik

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

karthikeyan-444867 (4/19/2010)
but it is producing the exact same execution plan as the one from Jeff.
Yes. I tested both the query plan...i didn't see any difference.

Thanks Karthikeyan!! I wonder how an ORDER BY clause will change the execution plan on both the methods...:unsure:

pYak Ten Centuries Points: 1027 More actions · Answer 11

Thanks COld

I love seeing how the same problem can be approached from different angles.

To tell the truth I tried to use the pivot operator with the data set

but couldn't get the desired output. So I appreciate your example.

Cheers

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

pYak (4/19/2010)
Thanks COld
I love seeing how the same problem can be approached from different angles.
To tell the truth I tried to use the pivot operator with the data set
but couldn't get the desired output. So I appreciate your example.
Cheers

Thanks and welcome buddy.. to be honest, the way you posted your question made me to sit and think of way, so thanks for that!!! secondly, SSC is such an awesome place to learn and hone our skills.. and finally, good to see proper response and appreciation from the OP!! 🙂

Cheers Guys!!

karthik M One Orange Chip Points: 29139 More actions · Answer 13

COldCoffee (4/19/2010)
karthikeyan-444867 (4/19/2010)
but it is producing the exact same execution plan as the one from Jeff.
Yes. I tested both the query plan...i didn't see any difference.
Thanks Karthikeyan!! I wonder how an ORDER BY clause will change the execution plan on both the methods...:unsure:

As you said i did the below changes...

#1)