June 28, 2012 at 8:50 am
How can I Pivot the following?
The objective is to get the columns listed horizontally and progamatically populate the first row of an Excel Spreadsheet in SSIS but I need help with the Pivot.
SELECT col.Name AS ColumnName
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
WHERE obj.type = 'U'
AND obj.name = 'Accounts_Payable'
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 8:57 am
the problem with pivots is that you seem to have to know what the columns are before you get the data
the only way you can do this is to use dynamic sql to build your pivot statement for you
MVDBA
June 28, 2012 at 9:24 am
I like Mike's CSV solution a lot better but in case you need to go the dynamic way this should be a starting point;
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
DECLARE @cols VARCHAR(2000)
DECLARE @cmd VARCHAR(1000)
SELECT col.Name AS ColumnName ,
obj.name AS ObjectName
INTO #tmp
FROM sys.Objects AS obj
INNER JOIN sys.columns AS Col ON obj.object_id = col.object_id
WHERE obj.type = 'U'
AND obj.name = 'Accounts_Payable'
SELECT @cols = STUFF(( SELECT DISTINCT
'],[' + ColumnName
FROM #tmp
ORDER BY '],[' + ColumnName
FOR
XML PATH('')
), 1, 2, '') + ']'
SET @cmd = N'SELECT ' + @cols + '
FROM
(
SELECT ColumnName, ObjectName
FROM #tmp
) p
PIVOT
(
COUNT([ColumnName])
FOR [ColumnName] IN
( ' + @cols + ' )
) AS pvt
'
EXEC (@cmd)
June 28, 2012 at 9:39 am
Thanks foe the sugesstions.
When I execute the dynamic sql I get the following error:
(169 row(s) affected)
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 9:43 am
Thanks for the CSV solution but what is up with the leading commas and trailing comma?
Why is each column prefixed with an @?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 10:01 am
Welsh Corgi (6/28/2012)
Thanks foe the sugesstions.When I execute the dynamic sql I get the following error:
(169 row(s) affected)
Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
Just print the @cmd variable to see what's wrong. Guessing your string would be truncated.. try changing the variable data types.
June 28, 2012 at 10:19 am
Yes it is truncating. I changed the Data Type to the VARCHAR(8000) and it still truncated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply