Pivot

Question

Pivot

Clark Bones

Mr or Mrs. 500

Points: 551
More actions
July 11, 2012 at 10:07 am

#264195

Hello!
I want to pivot a simple resultset, but can't seem to get it right. I am trying to use the PIVOT function.
I have attached an image of a sample which I hope shows you both the original data and the desired result. The true data is slightly more complicated, but not much.
If missing data is represented by 0 OR NULL is up to you.

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

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

Brandie Tarvin SSC Guru Points: 173083 More actions · Answer 1

Clark,

There are a lot of us that don't open attachments. Could you post your code in your reply, along with a CREATE TABLE statement (DDL) and an INSERT statement containing sample data? That would help us help you.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Clark Bones Mr or Mrs. 500 Points: 551 More actions · Answer 2

True enough, Brandie!

CREATE TABLE #BaseData(ClientNo int, Code varchar(3))

INSERT INTO #BaseData(ClientNo, Code)

VALUES

(123,'AAL')

,(1234,'AAC')

,(123,'AAC')

,(1234,'QPL')

SELECT * FROM #BaseData

CREATE TABLE #DesiredResult(ClientNo int, AAL varchar(3),AAC varchar(3),QPL varchar(3),)

INSERT INTO #DesiredResult(ClientNo, AAL, AAC, QPL)

VALUES

(123, 1, 1,0)

,(1234,0,1,1)

select * from #DesiredResult

Let me clarify that only one unique occurrence of ClientNo and a Code can exist. So in the Desiredresult-table the data reflects whether a code exists for a customer with bool values. So there is no need to sum the number of occurrences.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

You can use a PIVOT or CROSS-TAB as follows

SELECTClientNo,

SUM( CASE WHEN Code = 'AAL' THEN 1 ELSE 0 END ) AS AAL,

SUM( CASE WHEN Code = 'AAC' THEN 1 ELSE 0 END ) AS AAC,

SUM( CASE WHEN Code = 'QPL' THEN 1 ELSE 0 END ) AS QPL

FROM#BaseData

GROUP BY ClientNo

Check the links below for more information

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

http://www.sqlservercentral.com/articles/T-SQL/63681/

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

http://www.sqlservercentral.com/articles/Crosstab/65048/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ant-Green SSC Guru Points: 113509 More actions · Answer 4

Something like the below

SELECT

ClientNo,

[AAL],

[AAC],

[QPL]

FROM

(

SELECT * FROM #BaseData

) AS SourceData

PIVOT

(

COUNT(Code)

FOR Code IN ([AAL],[AAC],[QPL])

) as piv

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

Or if you wanted to do it dynamically:

CREATE TABLE #BaseData(ClientNo INT, Code VARCHAR(3))

INSERT INTO #BaseData(ClientNo, Code)

VALUES

(123,'AAL')

,(1234,'AAC')

,(123,'AAC')

,(1234,'QPL')

DECLARE

@sql VARCHAR(MAX)

,@COLUMNS VARCHAR(MAX)

SET @COLUMNS =

STUFF(( SELECT ',' + QUOTENAME(Code) AS [text()] FROM ( SELECT DISTINCT Code FROM #BaseData) AS Code

FOR XML PATH('')),1,1,'' )

SET @sql =

'SELECT *

FROM

(SELECT ClientNo, Code FROM #BaseData) AS A

PIVOT

(

COUNT(Code) FOR Code IN (' + @COLUMNS + ')

) AS PIV'

EXEC (@SQL)

DROP TABLE #BaseData

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe