Simple PIVOT Example?

Question

Simple PIVOT Example?

c2k

Ten Centuries

Points: 1090
More actions
January 24, 2012 at 11:44 am

#249720

Greeting extremely helpful SSCers. I've been struggling with trying to noodle through a data transformation problem. I have data in a table (TABLE A) that is structured in a manner that makes it easy to populate. However, that structure makes the reporting I am trying to do rather difficult. I need something that looks like TABLE B.
I believe the answer is to PIVOT the data using SQL but I am hitting a wall in getting the syntax correct (it is a brain twizzler for me). Can you provide some guidance on how I might perform this PIVOT from within a SQL statement?
Many thanks!
TABLE A
[DATE] [MIT] [NOMIT] [TOTAL]
12-1-11 1000 1000 2000
12-15-11 1250 750 2000
1-3-12 1500 500 2000
1-16-12 1750 250 2000
TABLE B
[DATE] [TYPE] [COUNT]
12-1-11 MIT 1000
12-1-11 NOMIT 1000
12-15-11 MIT 1250
12-15-11 NOMIT 750
1-3-12 MIT 1500
1-3-12 NOMIT 500
1-16-12 MIT 1750

Viewing 4 posts - 1 through 3 (of 3 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

It's more an UNPIVOT issue than a PIVOT.

Here's a code snippet based on your sample data. Please note the way the sample data are provided in a ready to use format.

DECLARE @tblA TABLE

(

[DATE] DATETIME,

[MIT] INT ,

[NOMIT] INT ,

[TOTAL] INT

)

SET DATEFORMAT mdy

INSERT INTO @tblA

VALUES

('12-1-11',1000 , 1000 , 2000),

('12-15-11', 1250 , 750 , 2000),

('1-3-12', 1500 , 500 , 2000),

('1-16-12', 1750 , 250 , 2000)

SELECT *

FROM @tblA

SELECT [DATE],[COUNT] , [TYPE]

FROM

(SELECT *

FROM @tblA) p

UNPIVOT

([COUNT] FOR [TYPE] IN

([MIT],[NOMIT] )

)AS unpvt

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]

c2k Ten Centuries Points: 1090 More actions · Answer 2

c2k

Ten Centuries

Points: 1090

January 24, 2012 at 1:54 pm

#1437951

Thank you very much.....that did the trick.

Paul White SSC Guru Points: 150467 More actions · Answer 3

DECLARE @a TABLE

(

TheDate date NOT NULL,

MIT integer NOT NULL ,

NoMIT integer NOT NULL,

Total integer NOT NULL

);

INSERT @a

(TheDate, MIT, NoMIT, Total)

VALUES

('2011-12-01', 1000, 1000, 2000),

('2011-12-15', 1250, 750, 2000),

('2012-01-03', 1500, 500, 2000),

('2012-01-16', 1750, 250, 2000);

SELECT

tableA.TheDate,

Transform.TheType,

Transform.TheCount

FROM @a AS tableA

CROSS APPLY

(

VALUES

('MIT', MIT),

('NoMIT', NoMIT)

) AS Transform (TheType, TheCount);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi