Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Expand / Collapse
Author
Message
Posted Tuesday, August 19, 2008 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 7,178, Visits: 13,630
Really nice to read, Jeff, and absolutely spot on.

I've had to do pivots in the last couple of weeks, with an unknown number of output (transposed) columns with unknown names, as in the following. It will be interesting to see your solution, you have a knack of coming up with "why the heck didn't I think of that?" solutions!

Cheers

ChrisM

SET NOCOUNT ON

CREATE TABLE #EDIInvoices (HospitalName VARCHAR(20), Purchaser VARCHAR(20), Sales money)
INSERT INTO #EDIInvoices (HospitalName, Purchaser, Sales)
SELECT 'Guy''s', 'BUPA', 10001 UNION ALL
SELECT 'Guy''s', 'Microsoft', 10002 UNION ALL
SELECT 'Guy''s', 'NIH', 10003 UNION ALL
SELECT 'Tommy''s', 'BUPA', 20001 UNION ALL
SELECT 'Tommy''s', 'Microsoft', 20002 UNION ALL
SELECT 'Tommy''s', 'NIH', 20003 UNION ALL
SELECT 'Tommy''s', 'Cornhill', 20004 UNION ALL
SELECT 'Raigmore', 'BUPA', 30001 UNION ALL
SELECT 'Raigmore', 'Microsoft', 30002 UNION ALL
SELECT 'Raigmore', 'NIH', 30003 UNION ALL
SELECT 'Raigmore', 'Cornhill', 30004 UNION ALL
SELECT 'Raigmore', 'HPA', 30005 UNION ALL
SELECT 'Raigmore', 'RaigmoreOnly', 30006 UNION ALL
SELECT 'Yorkhill', 'BUPA', 40001 UNION ALL
SELECT 'Yorkhill', 'Microsoft', 40002 UNION ALL
SELECT 'Yorkhill', 'Cornhill', 40004 UNION ALL
SELECT 'Yorkhill', 'HPA', 40005 UNION ALL
SELECT 'Yorkhill', 'YorkhillOnly', 40007

--SELECT * FROM #EDIInvoices -- Sanity check

-- PIVOT the results
DECLARE @SQLstr VARCHAR(5000)
SET @SQLstr = ''
SELECT @SQLstr = @SQLstr + CHAR(10) + ' MAX(CASE Purchaser WHEN ''' + Purchaser + ''' THEN Sales ELSE 0 END) AS [' + Purchaser + '], '
FROM (SELECT Purchaser FROM #EDIInvoices GROUP BY Purchaser) d

SET @SQLstr = 'SELECT HospitalName, ' + REVERSE(STUFF(REVERSE(@SQLstr),1,2,'')) + ' ' + CHAR(10) + 'INTO ##EDIInvoicesPivot FROM #EDIInvoices GROUP BY HospitalName'
PRINT @SQLstr -- sanity check

EXECUTE (@SQLstr)

SELECT * FROM ##EDIInvoicesPivot

DROP TABLE #EDIInvoices
DROP TABLE ##EDIInvoicesPivot



“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
Exploring Recursive CTEs by Example Dwain Camps
Post #555010
Posted Tuesday, August 19, 2008 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2008 6:45 AM
Points: 1, Visits: 11
Check out this article on Dynamic Crosstabs by Jeff Smith:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

-smc
Post #555022
Posted Tuesday, August 19, 2008 8:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 4, 2013 2:35 PM
Points: 92, Visits: 189
I am interested as to how or if we can pull a cross-tab or pivot dynamically. I have a query that can have an undetermined amount of comments associated to one job. In the query I need to show these comments in one line.

SET NOCOUNT ON

CREATE TABLE #BidComments (BidId int, Comment VARCHAR(varchar(max))
INSERT INTO #BidComments (BidId , Comment)
SELECT 1, 'This is comment 1 for bid 1' UNION ALL
SELECT 1, 'This is comment 2 for bid 1' UNION ALL
SELECT 1, 'This is comment 3 for bid 1' UNION ALL
SELECT 2, 'This is comment 1 for bid 2' UNION ALL
SELECT 2, 'This is the second comment for bid 2' UNION ALL
SELECT 2, 'Here we have the third comment for bid2' UNION ALL
SELECT 2, 'Comment 4' UNION ALL
SELECT 3, '1 comment on 3' UNION ALL
SELECT 3, 'comments for 3, the second' UNION ALL
SELECT 3, 'comment 3' UNION ALL
SELECT 3, 'Comment 4' UNION ALL
SELECT 3, 'Comment 5' UNION ALL

In the end I need to display it as

BidID Comment1 Comment2 Comment3



Any thoughts?
Post #555027
Posted Tuesday, August 19, 2008 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 4:05 AM
Points: 3, Visits: 114
Congratulations on a well-written and informative article.
Post #555068
Posted Tuesday, August 19, 2008 8:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Great article Jeff. Keep 'em coming.



ATB

Charles Kincaid

Post #555069
Posted Tuesday, August 19, 2008 9:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:27 PM
Points: 1,386, Visits: 824
Well Mr. Moden, you've done it again. A superbly useful article.
You're ongoing contributions are much appreciated.
Post #555083
Posted Tuesday, August 19, 2008 9:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 12:05 PM
Points: 3, Visits: 89
the pivot on aggregation function works on numbers, what I want is a pivot on literal/character. for example, instead of having sum(amount) to be pivoted, I'd like to see a varchar column to be concatenated.

create table d(tablename varchar(9), columnmane varchar(9))
insert into d values ('tableA','col1')
insert into d values ('tableA','col2')
insert into d values ('tableB','col1')
insert into d values ('tableB','col2')

I want to see a result like

tablename , columnlist
---------, ----------
tablea col1, col2
tableb col1, col2

I can only use cursor to program it for now, but like to see a solution with a single pivot sql statement if anyone know how to do it.


Jiulu Sun
Senior DBA; Oracle/MSSQL/Sybase; CTV television inc.
Post #555097
Posted Tuesday, August 19, 2008 9:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 266, Visits: 2,584
Once again, such good explanations/teaching.

I'm like an earlier poster. I haven't been able to find an advantage to the pivot syntax. (Though unpivot came in handy once.) I keep wondering and wondering why MS thought the pivot syntax would be a benefit. What do they see as the advantage over the simple CASE statement?

You not only pointed out the complexity of the pivot syntax, but gave us comparative performance stats too. Very helpful.

I'm like others on this posting in that I'm looking forward to seeing solutions on dynamic cross tabs/pivots.

Thanks. - JJ
Post #555130
Posted Tuesday, August 19, 2008 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 9, 2013 10:55 AM
Points: 8, Visits: 139
Nicely written article, Jeff! I'm new to this forum so don't know if you've covered it already, but it might be nice to see a counterpart to this article, Converting Columns to Rows. Maybe it doesn't warrant an entire article as I realize it is pretty straight forward, but it is common to recieve data in crosstabbed format also. The reason I mention it is that before I realized how easy it is to "uncrosstab" data using SQL, I used to have complex VB code in the UI to do it, which is VERY slow. I often find that if I take off the "programmer guy" hat and put on the "database guy" hat, the database can do things like that much more efficiently.

Post #555155
Posted Tuesday, August 19, 2008 10:23 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Jeff,
Nice work!

I think you did a great job of explaining pivots and crosstabs

Mark
Post #555171
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse