SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16382 Visits: 19554
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
sheila.clancy
sheila.clancy
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
marty.seed
marty.seed
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 199
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?
gerhard-356547
gerhard-356547
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 114
Congratulations on a well-written and informative article.
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1681 Visits: 2384
Great article Jeff. Keep 'em coming.

ATBCharles Kincaid
Andy Lennon
Andy Lennon
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 826
Well Mr. Moden, you've done it again. A superbly useful article.
You're ongoing contributions are much appreciated.
sunjiulu-708843
sunjiulu-708843
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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.
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 2859
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
Slope
Slope
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 143
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.
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1957 Visits: 711
Jeff,
Nice work!

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

Mark
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search