How to Pivot using T-SQL

Question

How to Pivot using T-SQL

sqlbi

SSC Eights!

Points: 901
More actions
August 26, 2010 at 1:05 pm

#387671

Hi,
my results looks in this format
Sorce YearMonth CountEntery
A 2003-04 43
B 2003-04 196
A 2003-05 135
B 2003-05 507
i want to the results like below format Using pivot here for yearmonth column will be keep on changing for every month
Source 2003-04 2003-05
A 43 135
B 1964 5079
Please help.. can anyone provide the query for the above scenario.
Thanks

Viewing 15 posts - 1 through 15 (of 23 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please have a look at the CrossTab article referenced in my signature for an alternative way.

The CrossTab method will also make it a lot easier to return dynamic yearmonth columns if needed. This concept is described in detail in the DynamicCrossTab article also referenced in my signature.

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]

sqlbi SSC Eights! Points: 901 More actions · Answer 2

The Link you provided is good, im trying it using pivot function in SQL 2008. can you help me with that?

WayneS SSC Guru Points: 95460 More actions · Answer 3

sqlbi (8/26/2010)
The Link you provided is good, im trying it using pivot function in SQL 2008. can you help me with that?

That article does an excellent job of walking you through how to do it. Just substitute your columns in the appropriate places.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sqlbi SSC Eights! Points: 901 More actions · Answer 4

i tired in so many ways and im getting error, in my table there are 277 records with different dates like and in nset month i may have 300 records with additional dates... can any one write simple query for my scenario.. thanks

2003-04

2003-05

LutzM SSC Guru Points: 107049 More actions · Answer 5

Step 1: remove those dozens (or even hudreds) of rather useless lines in your previous post.

Step 2: read the first article referenced in my signature

Step 3: provide ready to use sample data following the guideline described in step 2

Step 4: add your expected output based on your sample data together with what you've tried so far and where you got stuck (I'd expect to see some code based on Jeff's CrossTab article).

We're here to help you but you should also help us help you.

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]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

sqlbi (8/26/2010)
i tired in so many ways and im getting error...

Cool... let's see what you've tried. Also, take a peek at the first link in my signature line below. It'll help you get better help a lot more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 7

sqlbi - You might want to take a look at this post - this poster did everything right for how to get helpful answers. And the answer is right in line with what you need to be looking at for your solution.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 8

I hope this would work for you:

SELECT * FROM (SELECT Source, YearMonth, CountEntery FROM tbl) AS a

PIVOT(SUM(CountEntery) FOR YearMonth IN (2003-04,2003-05)) AS p

sqlbi SSC Eights! Points: 901 More actions · Answer 9

i tried with below query i getting syntax error as

Msg 1033, Level 15, State 1, Line 19

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Msg 102, Level 15, State 1, Line 21

Incorrect syntax near '2003'.

The script i Used is

with CTETemp

as(

Select CASE

When SourceCode = 'WENR' Then 'A'

When SourceCode = 'WCHG' Then 'B'

When SourceCode = 'EVLV' Then 'C'

Else 'Other'

End as Sorce,

Substring(CONVERT(varchar(19),RegisterTimestamp,120),1,7) as YearMonth,

COUNT(EmailAddrID) As CountEntery

From Warehouse.dbo.tblCustomerEmailAddress

Group By Substring(CONVERT(varchar(19),RegisterTimestamp,120),1,7),

Case When SourceCode = 'WENR' Then 'A'

When SourceCode = 'WCHG' Then 'B'

When SourceCode = 'EVLV' Then 'C'

Else 'Other'

End

Order By Substring(CONVERT(varchar(19),RegisterTimestamp,120),1,7) ,Sorce

)

SELECT * FROM (SELECT Sorce, YearMonth, CountEntery FROM CTETemp) AS a

PIVOT(COUNT(Emailaddrid) FOR YearMonth IN (2003-04,2003-05)) AS p

WayneS SSC Guru Points: 95460 More actions · Answer 10

take out this line of code...

Order By Substring(CONVERT(varchar(19),RegisterTimestamp,120),1,7) ,Sorce

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sqlbi SSC Eights! Points: 901 More actions · Answer 11

still getting Syntax error as

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near '2003'.

WayneS SSC Guru Points: 95460 More actions · Answer 12

So, where all do you have '2003'?

Oh, it's here:

(2003-04,2003-05))

how about putting those in quotes, like:

('2003-04','2003-05'))

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sqlbi SSC Eights! Points: 901 More actions · Answer 13

It doesn't help basically i want it to be in.. wether im doing mistake some where in the script?

2008-022008-032008-042008-052008-06

A 123 234 132 245 300

B 1235 2546 541 1234 457

C 1457 1456 545 145 2546

LutzM SSC Guru Points: 107049 More actions · Answer 14

sqlbi (8/27/2010)
It doesn't help basically i want it to be in.. wether im doing mistake some where in the script?
2008-022008-032008-042008-052008-06
A 123 234 132 245 300
B 1235 2546 541 1234 457
C 1457 1456 545 145 2546

It seems like you really don't want to get help.

You ignore the advice Wayne gave you (even a link showing you the effect of ready to use sample data), Jeffs advice and the stuff I recommended. And I don't think it's a language barrier...

@Wayne: the OP is all yours!

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]