Creating crosstab/pivot table

Question

Creating crosstab/pivot table

Nomvula

Hall of Fame

Points: 3778
More actions
September 5, 2008 at 6:40 am

#99259

hi guys
i have data looking like this
CodePanel NoBay NoClosing CodeTest Product Oil Quality
CB039700H N/A 265
i need to create a query so that my data look like this
CodeAttributeCode AttributeValue
CB0397Panel No 0
CB0397Bay No 0
CB0397Closing Code H
CB0397Test Product n/a
CB0397Oil Quality 265
PLEASE HELP

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

[font="Courier New"]DROP TABLE #Temp

CREATE TABLE #Temp ([Code] VARCHAR(6), [Panel No] INT, [Bay No] INT, [Closing Code] CHAR(1), [Test Product] VARCHAR(3), [Oil Quality] INT)

INSERT INTO #Temp ([Code], [Panel No], [Bay No], [Closing Code], [Test Product], [Oil Quality])

SELECT 'CB0397', 0, 0, 'H', 'N/A', 265

SELECT [Code], 'Panel No' AS [AttributeCode], CAST([Panel No] AS VARCHAR) AS [AttributeValue] FROM #Temp UNION ALL

SELECT [Code], 'Bay No', CAST([Bay No] AS VARCHAR) FROM #Temp UNION ALL

SELECT [Code], 'Closing Code', [Closing Code] FROM #Temp UNION ALL

SELECT [Code], 'Test Product', [Test Product] FROM #Temp UNION ALL

SELECT [Code], 'Oil Quality', CAST([Oil Quality] AS VARCHAR) FROM #Temp [/font]

Output:

Code AttributeCode AttributeValue

------ ------------- ------------------------------

CB0397 Panel No 0

CB0397 Bay No 0

CB0397 Closing Code H

CB0397 Test Product N/A

CB0397 Oil Quality 265

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HI,

Here is another example using the new SQL 2005 UNPIVOT operator:

DROP TABLE #Temp

CREATE TABLE #Temp ([Code] VARCHAR(6), [Panel No] INT, [Bay No] INT, [Closing Code] CHAR(1),

[Test Product] VARCHAR(3), [Oil Quality] INT)

INSERT INTO #Temp ([Code], [Panel No], [Bay No], [Closing Code], [Test Product], [Oil Quality])

SELECT 'CB0397', 0, 0, 'H', 'N/A', 265

SELECT [Code],[AttributeCode],[AttributeValue]

FROM

(SELECT

[Code],

CAST([Panel No] AS VARCHAR) as [Panel No],

CAST([Test Product] AS VARCHAR) as [Test Product],

CAST([Bay No] AS VARCHAR) as [Bay No],

CAST([Closing Code] AS VARCHAR) as [Closing Code],

CAST([Oil Quality] AS VARCHAR) as [Oil Quality]

FROM #Temp) p

UNPIVOT

([AttributeValue] FOR [AttributeCode] IN

([Test Product],[Panel No], [Bay No],[Closing Code])

)AS unpvt

GO

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

GSquared SSC Guru Points: 260824 More actions · Answer 3

I'd go with Unpivot on this one. Performance on it has been really quite good in my tests.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

Thanks G,

I was going to actually post something to ask which was a better performer 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Nomvula Hall of Fame Points: 3778 More actions · Answer 5

hi guys

sorry i took some time to respond, i was out of the office for quite sometime and i'd like to thank everyone who participated in this thread.

you guys rock