Problem Using "PIVOT" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem Using "PIVOT"

Author

Topic

ruchirj07
Starting Member

37 Posts

Posted - 2008-01-22 : 08:19:25

Hi,

SELECT * FROM
(SELECT col a,col b,col c
FROM table a) g
PIVOT
(SUM(col c)
FOR col b in (@tmp)) as pvt order by atestcaseid

Can we use variable(@tmp) in PIVOT?
If yes, is there any specific syntax to use it coz setting up the value of @tmp at the beginning does not help the cause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 08:25:27

1. Read all about dynamic SQL here http://www.sommarskog.se/dynamic_sql.html
2. Make sure your database is set to Compatibility Mode 90 or higher.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 08:27:07

[code]DECLARE @SQL VARCHAR(8000)

SET @SQL = 'SELECT * FROM TableA AS g PIVOT (SUM(ColC) FOR ColB IN (' + @Tmp + ')) AS pvt ORDER BY atestcaseid'
PRINT @SQL
EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

ruchirj07
Starting Member

37 Posts

Posted - 2008-01-22 : 08:34:18

Thanks Peso.

One more doubt, how can i obtain the latest value from the table from the query above?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-22 : 08:37:47

Peso, can you provide some information as to why a variable cannot be used normally. Why one has to resort to dynamic query. If you can provide some link on net. I tried to search on google but could not find.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 08:42:14

Try [url]ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/36b19e68-94f6-4539-aeb1-79f5312e4263.htm[/url]
See Section L.

E 12°55'05.25"
N 56°04'39.16"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-22 : 08:55:11

Peso, I read the link provide by you.It has following example
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;

The reason I can think of (may be incorrect) of using dynamic sql can be seen in select statement. If we are using variable for our grouping columns in PIVOT, we can not include them in our select query since it is variable holding column names.But, when we are using dynamic sql, we can access those columns in our select query ans use result. Please make me correct if I am getting this incorrect.

ruchirj07
Starting Member

37 Posts

Posted - 2008-01-22 : 09:28:51

Is there any way to display the actual value of column instead of count or sum?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 09:30:26

You must hardwire the column names with the built-in PIVOT operator.
There are other options, but they are all dynamic.

E 12°55'05.25"
N 56°04'39.16"

ruchirj07
Starting Member

37 Posts

Posted - 2008-01-22 : 09:34:05