PIVOT CLAUSE

Question

PIVOT CLAUSE

mhilligas

Ten Centuries

Points: 1354
More actions
November 2, 2009 at 5:00 pm

#216285

I need to learn how to PIVOT.
My data is as follows:
TIME_STAMP TEST_CODE TEST_VALUE
11/2/09 3:00:00 PM TEST_A 30
11/2/09 3:00:00 PM TEST_B 50
11/2/09 3:00:00 PM TEST_C 16
11/2/09 4:00:00 PM TEST_A 25
11/2/09 4:00:00 PM TEST_B 45
11/2/09 4:00:00 PM TEST_C 12
I would like to see it as:
TEST_CODE 11/2/09 3:00:00 PM 11/2/09 4:00:00 PM
TEST_A 30 25
TEST_B 50 45
TEST_C 16 12
Can anyone help. I believe PIVOT can do this.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Can you post the Script for create Table and Insert Data into, so that we would be able to use that and setup the same at our end.

Use the SQL Code, tags while posting to give a better idea.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

mhilligas Ten Centuries Points: 1354 More actions · Answer 2

Here is the Create Table Script and a CSV file to import for the data.

TempTableForPIVOT.txt

TempTableForPIVOT_Data.txt

I would liketo see the output pivot to look like this the enclosed excel sheet.

PivotOutput.xlsx

LutzM SSC Guru Points: 107049 More actions · Answer 3

In general, PIVOT can give you the requested result.

But when looking at your scenario it seems like you don't want to modify your pivot statement whenever you have a new date range. With PIVOT you would need to do it, unfortunately. (Unless you'd use dynamic pivot...)

What seems to be more applicable to your scenario would be a dynamic cross tab as described in the last article referenced in my signature.

Please follow the link and see if you can modify the given sample to your needs. If not, get back to us for more assistance.

Side note: please also have a look at the first link in my signature on how to post sample data. Some of us do expect data in a ready to use format to get focused on the subject right away with no need messing around with EXCEL data...

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]

mhilligas Ten Centuries Points: 1354 More actions · Answer 4

Lutz,

It is nice to hear from you again.

Thanks for the advice. I have looked at the articles.

I have a working knowledge of SQL, but this is getting pretty intense for me.

I will try and make sense of it all.

Regards

Michael Hilligas

LutzM SSC Guru Points: 107049 More actions · Answer 5

Hi,

it took me a while so set up a step by step "tutorial" on how to convert a static cross tab into a dynamic cross tab using your sample data (would have helped a lot if the data would have been presented in a ready to use format...).

I hope it makes it a little easier to understand since it's using your data...

SET NOCOUNT ON

-- Static Cross Tab:

-- part 1

SELECT

TestID

,TestCode

-- part 2

,MAX(CASE WHEN TS='2009-11-02 15:33:38.000' THEN testvalue else '' END) as [11/02/2009 3:33PM]

,MAX(CASE WHEN TS='2009-11-02 15:34:19.000' THEN testvalue else '' END) as [11/02/2009 3:34PM]

,MAX(CASE WHEN TS='2009-11-02 15:35:20.000' THEN testvalue else '' END) as [11/02/2009 3:35PM]

-- part 3

FROM #TempTableForPIVOT

GROUP BY TestID,TestCode

order by testid

/*

Part 1 and part 3 will remain static in a dynamic cross part.

The second part can be rewritten as being dynamic (please see result grid for details):*/

SELECT ',

MAX(CASE WHEN TS=''' + convert(char(19),TS,120) + ''' THEN testvalue else '''' END) as ['+ convert(char(10),TS,101) +' ' + right(convert(char(19),TS,100),7)+']'

FROM #TempTableForPIVOT

GROUP BY TS

ORDER BY TS

--next step would be to store all three parts in variables:

DECLARE @sql1 varchar(100)

DECLARE @sql2 varchar(2000)

DECLARE @sql3 varchar(100)

SELECT @sql1 =

'SELECT

TestID

,TestCode

'

SELECT @sql2 =

-- note: please see how the parameter is added up with each row...

COALESCE(@sql2,'') +

',MAX(CASE WHEN TS=''' + convert(char(19),TS,120) + ''' THEN testvalue else '''' END) as ['+ convert(char(10),TS,101) +' ' + right(convert(char(19),TS,100),7)+']

'

FROM #TempTableForPIVOT

GROUP BY TS

ORDER BY TS

SELECT @sql3 =

'FROM #TempTableForPIVOT

GROUP BY TestID,TestCode

order by testid'

-- print the final statement

PRINT @sql1 + @sql2 + @sql3

-- or execute it

--EXEC (@sql1+@sql2+@sql3)

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]

mhilligas Ten Centuries Points: 1354 More actions · Answer 6

Lutz,

Thank you very much!!!!

This will be very helpful

Sorry about the data....It will not happen again.

Regards

Michael Hilligas

LutzM SSC Guru Points: 107049 More actions · Answer 7

You're very welcome!

Actually, I felt like I had to do some "charity work" since I had a real bad day a work...

But now I'm feeling better! Thank you for your feedback 🙂

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]

mhilligas Ten Centuries Points: 1354 More actions · Answer 8

Charity is good for the SOUL, and in this case ME 🙂

Have a good week.

Mike