SQL Server pivot on two columns

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


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

LVL 35
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.
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

Brian CroweDatabase AdministratorCommented:
Thank you, how extensible does this need to be?  Do the "Field" columns in the table need to be dynamic?
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Brian CroweDatabase AdministratorCommented:
What about something like this:

	Title	VARCHAR(50),
	[Key]	VARCHAR(50),
	Value	INT,
	Units	VARCHAR(10)

INSERT INTO @Table1 (Title, [Key], Value, Units)
VALUES ('Title1','Field_1','4000','lbs'),

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

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
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.
YZlatAuthor Commented:
Thanks, I will give it a shot
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:

PageNumber int
LineNumber  int

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

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?
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!
Brian CroweDatabase AdministratorCommented:
It would be great if you could post your code for anyone else that might happen upon this thread.
YZlatAuthor Commented:
Sure. Here it is:

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]
(SELECT Title, [Key],IIF([Units]<>'',[Value] + '/' +[Units],    
[Value]) AS ValueUnits FROM Table1 WHERE [Key] LIKE 'Field_%') as Data 
FOR [Key] IN([Field_1],[Field_2],[Field_3])) As Piv

Open in new window

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.