SQL Server pivot on two columns

In have a table of a key-value pair format:

Title
Key
Value
Units


Where Title is a varchar, Key field contains field name, aValue contains the value and Units contains the unit for the Key if applicable.

I created a pivot that displays data in a tabular format:

Title        Field_1 
----------------------------
Title1      3000 lbs
Title2      250 ml

Open in new window


by using:

SELECT * FROM
(SELECT Title, Key, IIF(NOT Units IS NULL, [Value] + ' ' + [Units],[Value]) AS ValueUnits
FROM Table1 WHERE Key LIKE 'Field_%') AS Data
PIVOT(MAX([Value]) FOR [Key] IN ([Field_1],[Field_2],[Field_3])
) as PivData

Open in new window


I need to display units as a separate column, i.e. to pivot on two columns. How can I accomplish that?

Title      Field1      Units
---------------------------------------
Title1    3000         lbs
Title2    250             ml

Open in new window

T
LVL 35
YZlatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Could you provide some sample data?  Preferably in the form of insert statements.
0
YZlatAuthor Commented:
INSERT INTO Table1(Title,[Key],[Value],Units)VALUES('Title1','Field_1','4000','lbs')
INSERT INTO Table1(Title,[Key],[Value],Units)VALUES('Title1','Field_2','150','pages')
INSERT INTO Table1(Title,[Key],[Value],Units)VALUES('Title1','Field_3','200','ml')
INSERT INTO Table1(Title,[Key],[Value],Units)VALUES('Title2','Field_2','300','pages')
INSERT INTO Table1(Title,[Key],[Value],Units)VALUES('Title3','Field_1','350','lbs')
INSERT INTO Table1(Title,[Key],[Value],Units)VALUES('Title3','Field_3','55','ml')

Open in new window

0
Brian CroweDatabase AdministratorCommented:
Thank you, how extensible does this need to be?  Do the "Field" columns in the table need to be dynamic?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Brian CroweDatabase AdministratorCommented:
What about something like this:

DECLARE @Table1 TABLE
(
	Title	VARCHAR(50),
	[Key]	VARCHAR(50),
	Value	INT,
	Units	VARCHAR(10)
);

INSERT INTO @Table1 (Title, [Key], Value, Units)
VALUES ('Title1','Field_1','4000','lbs'),
	('Title1','Field_2','150','pages'),
	('Title1','Field_3','200','ml'),
	('Title2','Field_2','300','pages'),
	('Title3','Field_1','350','lbs'),
	('Title3','Field_3','55','ml');

SELECT * FROM @Table1 ORDER BY [Key], Value DESC;

WITH cteTable1 AS
(
	SELECT Title, [Key], Value, Units,
		--RowNumber is used to make sure we get the  MAX value for a given Title/Key
		ROW_NUMBER() OVER(PARTITION BY Title, [Key] ORDER BY Value DESC) AS RowNumber
	FROM @Table1
)
SELECT cte0.Title,
	cte1.Value AS 'Field_1_Value', cte1.Units AS 'Field_1_Units',
	cte2.Value AS 'Field_2_Value', cte2.Units AS 'Field_2_Units',
	cte3.Value AS 'Field_3_Value', cte3.Units AS 'Field_3_Units'
FROM
(
	SELECT DISTINCT Title FROM cteTable1	
) AS cte0
LEFT OUTER JOIN cteTable1 AS cte1
	ON cte0.Title = cte1.Title
	AND cte1.RowNumber = 1
	AND cte1.[Key] = 'Field_1'
LEFT OUTER JOIN cteTable1 AS cte2
	ON cte0.Title = cte2.Title
	AND cte2.RowNumber = 1
	AND cte2.[Key] = 'Field_2'
LEFT OUTER JOIN cteTable1 AS cte3
	ON cte0.Title = cte3.Title
	AND cte3.RowNumber = 1
	AND cte3.[Key] = 'Field_3'
ORDER BY cte0.Title

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YZlatAuthor Commented:
All the Key columns I need will start from the same prefix - "Field_" in this case. Could this be done using a pivot? Without creating a separate table. I need to put this code into a view or stored procedure
0
Brian CroweDatabase AdministratorCommented:
I don't see any way to do this using a PIVOT.  Any examples I've found online use alternate methods when multiple columns are involved.

There was no separate table created here.  The CTE is only necessary if there is a possibility of having more than one value for a given Title & Key.  To make it extensible you may need to use dynamic sql and create the query.
0
YZlatAuthor Commented:
Thanks, I will give it a shot
0
YZlatAuthor Commented:
One more thing. The actual table has couple more fields that I need to order data by. The actual table is as follows:

Title
PageNumber int
LineNumber  int
 Key
 Value
 Units

Is it possible for you to add those two fields to the code, ordering data by Title, then PageNumber, and by LineNumber?

I'd really appreciate that

INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title1',1,0,'Field_1','4000','lbs')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title1',1,0,'Field_2','150','pages')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title1',2,0,'Field_1','600','lbs')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title1',2,0,'Field_2','40','pages')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title1',1,0,'Field_3','200','ml')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title2',1,0,'Field_2','300','pages')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title3',1,0,'Field_1','350','lbs')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title3',2,0,'Field_1','111','lbs')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title3',1,0,'Field_3','1000','ml')
INSERT INTO Table1(Title,PageNumber,LineNumber,[Key],[Value],Units)VALUES('Title3',2,0,'Field_3','155','ml')

Open in new window

0
Brian CroweDatabase AdministratorCommented:
I'm not clear on how those fields affect the output?  Are they grouping fields or just ordering fields for determining the MAX value?
0
YZlatAuthor Commented:
Actually I figured out another - easier way to do this - When concatenating Value and Units I use "/" as a divider and then, in my select list, I just split the field on this divider and display two separate fields - value and unit.

Thank you so much for your help!
0
Brian CroweDatabase AdministratorCommented:
It would be great if you could post your code for anyone else that might happen upon this thread.
0
YZlatAuthor Commented:
Sure. Here it is:

SELECT Title,
SUBSTRING([Field_1],0,CHARINDEX('/',[Field_1])) AS [Field_1],
SUBSTRING([Field_1],CHARINDEX('/',[Field_1])+1,LEN([Field_1]-1) AS[Field_1Units], 
SUBSTRING([Field_2],0,CHARINDEX('/',[Field_2])) AS [Field_2],
SUBSTRING([Field_2],CHARINDEX('/',[Field_2])+1,LEN([Field_2]-1) AS [Field_2Units], 
SUBSTRING([Field_3],0,CHARINDEX('/',[Field_3])) AS [Field_3],
SUBSTRING([Field_3],CHARINDEX('/',[Field_3])+1,LEN([Field_3]-1) AS [Field_3Units]
FROM
(SELECT Title, [Key],IIF([Units]<>'',[Value] + '/' +[Units],    
[Value]) AS ValueUnits FROM Table1 WHERE [Key] LIKE 'Field_%') as Data 
PIVOT(
MAX([ValueUnits])
FOR [Key] IN([Field_1],[Field_2],[Field_3])) As Piv
ORDER BY Title

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.