Rows to Columns

Question

Post reply

Rows to Columns

ravindra.gangadhar

SSC Enthusiast

Points: 102
More actions
December 27, 2007 at 12:52 am

#377743

Hi Guys,
I have table which retrives values and i want to convert those values to columns but the values are not fixed it may increase.
example
ID TEXT
------------------------
1 US
2 UK
3 SINGAPORE
etc....
Required Result.
US UK SINGAPORE 'ETC.......(It may be around 20 to 30 values max to convert to columns)
Note: Using SQL SERVER 2000
Please advise.

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

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

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

[font="Courier New"]

use PIVOT

example:

DECLARE @tbl TABLE (Country VARCHAR(10), Sales MONEY)

INSERT INTO @tbl (Country, Sales)

SELECT 'UK', 250 UNION ALL

SELECT 'USA', 400 UNION ALL

SELECT 'INDIA', 500

SELECT * FROM @tbl

PIVOT (

SUM(Sales)

FOR Country IN (UK, USA, India)

) a

/*

OUTPUT

UK USA India

------------------------------

250.0000 400.0000 500.0000

*/[/font]

.

ravindra.gangadhar SSC Enthusiast Points: 102 More actions · Answer 2

Hi Jacob,

Thanks for the query, the country filed is dynamic i cannot check using the case statment.

Please advise.

ravindra.gangadhar SSC Enthusiast Points: 102 More actions · Answer 3

Hi Jacob,

Thanks for the query, the country filed is dynamic i cannot check using the case statment.

Please advise.

ravindra.gangadhar SSC Enthusiast Points: 102 More actions · Answer 4

Hi Jacob,

Thanks for the query, the country filed is dynamic i cannot check using the case statment.

Please advise.

jacob sebastian SSChampion Points: 11822 More actions · Answer 5

I guess we need to know the columns in advance to do a pivot operation. Probably you should make a list of countries and add it in the FOR() clause.

[font="Courier New"]DECLARE @tbl TABLE (Country VARCHAR(10), Sales MONEY)

INSERT INTO @tbl (Country, Sales)

SELECT 'UK', 250 UNION ALL

SELECT 'USA', 400 UNION ALL

SELECT 'INDIA', 500

SELECT * FROM @tbl

PIVOT (

SUM(Sales)

FOR Country IN (UK, USA, India, china, France, Italy, Spanish)

) a[/font]

.

joao.salgueiro SSC Eights! Points: 941 More actions · Answer 6

Hi

You could do something like this...

Select all the countries and then use the EXECUTE Statement

declare @countries varchar(200)

declare @country varchar(20)

declare @piv varchar(1000)

set @countries = ''

declare cur cursor for

select country from tbl

open cur

Fetch NEXT From cur

Into @country

IF @@FETCH_STATUS <> 0

PRINT ' '

While @@FETCH_STATUS = 0

Begin

set @countries = @countries + @country

Fetch NEXT From cur

Into @country

if @@FETCH_STATUS = 0

set @countries = @countries + ', '

End

Close cur

Deallocate cur

set @piv = '

select * from tbl

pivot (sum(sales)

for country in (' + @countries + ')) a'

execute (@piv)

Jeff Moden SSC Guru Points: 1004253 More actions · Answer 7

Oh my... we've gone from using a new function that (apparently) can't do the job, to using a Cursor...

Heh... Let's get back to the basics folks!

Consider the following PIVOT example from Books Online...

USE AdventureWorks;

GO

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5

FROM

(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt

ORDER BY VendorID

Personnally, I'm not impressed... everything is hard-coded so far as the EmployeeID goes. Also, within the "IN" in the Pivot, I've been pretty much unsuccessful using a simple SELECT instead of a hard-coded list. Even if I could, the outer SELECT seems to require some hard-coding to identiry column names in this case.

Now, take a look at the "old school" Dynamic SQL method of Cross-Tabs...

--===== Identify the database to use

USE AdventureWorks

GO

--===== Declare the dynamic SQL variables

DECLARE @SQL1 VARCHAR(MAX)

DECLARE @SQL2 VARCHAR(MAX)

DECLARE @SQL3 VARCHAR(MAX)

--===== Create the SELECT and non-aggragated column(s)

SELECT @SQL1 = 'SELECT VendorID,' + CHAR(10)

--===== Create the aggragated list using the data as the driver for column names

SELECT @SQL2 = ISNULL(@SQL2 + ','+ CHAR(10),'')

+ 'STR(SUM(CASE WHEN EmployeeID = ' + CONVERT(VARCHAR(10),EmployeeID)

+ ' THEN 1 ELSE 0 END),12) AS Emp' + CONVERT(VARCHAR(10),EmployeeID) + '_Count' + ',' + CHAR(10)

+ 'STR(SUM(CASE WHEN EmployeeID = ' + CONVERT(VARCHAR(10),EmployeeID)

+ ' THEN SubTotal ELSE 0 END),12,2) AS Emp' + CONVERT(VARCHAR(10),EmployeeID) + '_Sales'

FROM Purchasing.PurchaseOrderHeader

GROUP BY EmployeeID

--===== Add line totals to the aggragated column names

SELECT @SQL2 = @SQL2 + ','+ CHAR(10)

+ 'STR(COUNT(*),12) AS Total_Count' + ',' + CHAR(10)

+ 'STR(SUM(SubTotal),12,2) AS Total_Sales' + CHAR(10)

--===== Create the FROM, GROUP BY, and ORDER BY

SELECT @SQL3 = 'FROM Purchasing.PurchaseOrderHeader' + CHAR(10)

+ 'GROUP BY VendorID' + CHAR(10)

+ 'ORDER BY VendorID'

--===== Execute the dynamic SQL to create the report

-- Uncomment the PRINT statement if you want to see the SQL

--PRINT @SQL1 + @SQL2 + @SQL3

EXEC (@SQL1 + @SQL2 + @SQL3)

Dunno... maybe I'm an idiot and just don't know how to use Pivot. If that's true, someone show us how to use Pivot to dynamically expand the columns like I did in the code above. In fact, show us how to use PIVOT to make the same report as I did in the code above. Not being a smart-guy about this... would really like to learn because, right now, PIVOT is looking pretty useless to me.

--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)

jacob sebastian SSChampion Points: 11822 More actions · Answer 8

I agree with Jeff that PIVOT operator is not that useful in the specific task that we saw. Infact, it is not very helpful with variable number of columns.

However, i am using PIVOT in several reports where I had fixed number of columns. It includes sales figures of last 12 months, of quarter, weekly data etc etc.

I saw that PIVOT is much faster than doing subqueries or CASE statements to transform aggregated data to columns.

I think, even when we need dynamic columns, a DYNAMIC PIVOT QUERY might be a better choice. But of course, do not use a CURSOR. Just use a SELECT query to build the columns list...

DECLARE @countries VARCHAR(1000)

SET @countries = ''

select @countries = @countries + CASE WHEN LEN(@Countries) > 1 THEN ',' ELSE '' END + CountryName

FROM Countries

.

Jeff Moden SSC Guru Points: 1004253 More actions · Answer 9

I saw that PIVOT is much faster than doing subqueries or CASE statements to transform aggregated data to columns

Jacob, if you have an example of that and a smattering of what the data is, I sure wouldn't mind doing a performance test.

--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)

jacob sebastian SSChampion Points: 11822 More actions · Answer 10

Hi Jeff,

I must admit that my previous statement did not turn to be 100% correct. I just created a test table and populated some test data. I did some testing, and I did not really find a considerable performance differences by using PIVOT.

I was under the impression that PIVOT is much better than the old approach after trying it while optimizing a stored procedure recently. Though it was helpful on that specific situation, there does not seem to be a difference in most of the cases I tested. I think behind the screens SQL Server does the same old trick.

Thank you for clearing this point.

.

Jeff Moden SSC Guru Points: 1004253 More actions · Answer 11

Thanks for the feedback, Jacob...

Yeah, the "same old trick" you're be speaking about is the aggragates themselves.

--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)

Adam Haines SSC-Insane Points: 23217 More actions · Answer 12

Dynamic pivot has limited flexibility, as you can only aggregate one column at a time. There are plenty workarounds to accomplish multiple aggregations but the logic becomes overly complicated.

I will continue to stick to the basics and PIVOT will continue to sit on the backburner, until it becomes useful.

As an FYI, this is an example of how one would gather dynamic columns versus "hard coding"

DECLARE @cols NVARCHAR(2000)

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT

'],[' + LTRIM(STR(t2.EmployeeID))

FROM Purchasing.PurchaseOrderHeader AS t2

ORDER BY '],[' + LTRIM(STR(t2.EmployeeID))

FOR XML PATH('')

), 1, 2, '') + ']'

DECLARE @sql AS nvarchar(MAX)

SET @sql = N'

SELECT VendorID, ' + @cols + '

FROM

(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( ' + @cols + ' )

) AS pvt

ORDER BY VendorID'

EXEC sp_executesql @sql

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004253 More actions · Answer 13

Heh... that about does it for me, Adam... I'm going to pivot the PIVOT command right into Unit 0 and use the old school methods I demo'd for this type of stuff.

--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)

Todd Townley Ten Centuries Points: 1320 More actions · Answer 14

Real world example using dynamic pivot:

I have processes where I want to run a series of tests gathering specific record counts at specific times. I collect the results of those tests in a table. I then want to display the results where each row is one test and each column is a test time.

Here is the table with two records:

CREATE TABLE [dbo].[XREF_RecordCountResults](

[Ind] [int] IDENTITY(1,1) NOT NULL,

[Test] [varchar](250) NOT NULL,

[SubTest] [varchar](250) NULL,

[TestOrder] [int] NOT NULL,

[ResultDesc] [varchar](250) NULL,

[Results] [int] NULL,

[TestDate] [datetime] NOT NULL,

CONSTRAINT [PK_XREF_RecordCountResults] PRIMARY KEY CLUSTERED

([Ind] ASC) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO xref_recordcountresults ([Test], [SubTest], [TestOrder], [ResultDesc], [Results], [TestDate])

VALUES ('Jackets', 'SubTest Classification', 300, 'Jacket test', 1389, '2007-12-21 17:00:28')

INSERT INTO xref_recordcountresults ([Test], [SubTest], [TestOrder], [ResultDesc], [Results], [TestDate])

VALUES ('Jackets', 'SubTest Classification', 300, 'Jacket test', 211145, '2007-12-22 01:31:30')

Here is the code that generates the report:

DECLARE@SQLvarchar(8000)

SET @SQL = 'SELECT TestOrder, Test, SubTest, ResultDesc'

SELECT@SQL = @SQL + ', [' + CONVERT(varchar, TestDate, 121) + ']'

FROMXREF_RecordCountResults

GROUP BY TestDate

ORDER BY TestDate

SET @SQL = @SQL + '

FROM ( SELECT TestOrder, Test, SubTest, ResultDesc, Results, CONVERT(varchar, TestDate, 121) AS TestDateChar

FROM XREF_RecordCountResults) x

PIVOT (

SUM(Results) FOR TestDateChar IN ('

SELECT@SQL = @SQL + ', [' + CONVERT(varchar, TestDate, 121) + ']'

FROMXREF_RecordCountResults

GROUP BY TestDate

ORDER BY TestDate

SET @SQL = @SQL + ')

) AS p'

SET @SQL = REPLACE(@SQL, ' IN (, ', ' IN (') --Removes unneeded leading comma

--print @sql

exec (@SQL)

Add one more test:

INSERT INTO xref_recordcountresults ([Test], [SubTest], [TestOrder], [ResultDesc], [Results], [TestDate])

VALUES ('Jackets', 'SubTest Classification', 300, 'Jacket test', 211792, '2007-12-22 05:28:12')

Rerun the report and note that the new column is picked up.