Display Columns as Rows From Temp Table

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 2008 Forums

Transact-SQL (2008)

Display Columns as Rows From Temp Table

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-03 : 23:16:33

Hi,
Here we exporting an CSV file by OPENROWSET and getting those results in a Temp table.

Ex: Temp Table --> #TempCOL
In the above table columns will be in 'n' numbers, with some sample values like

[Col1] [Col2] [Col3] .... [coln]
A B C .... Z
a b c .... z

Need the above results Vertically, like Column names in Rows and with their respective values like

[COLUMN] [VALUE1] [VALUE2]
Col1 A a
Col2 B b
Col3 C c
. . .
. . .
. . .
Coln Z z

Please help me in this to get the Solution. Its a major requirement for us.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 06:09:32

use UNPIVOT


SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS Rn,* FROM Table) t
UNPIVOT (Value FOR COlumn IN ([Col1], [Col2], [Col3], .... [coln]))u

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learntsql

524 Posts

Posted - 2012-01-04 : 06:50:49

Hi Visakhm,

Is this possible to get the values dynamically for IN clause?
TIA.

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-04 : 11:25:56

quote:
Originally posted by learntsql

Hi Visakhm,

Is this possible to get the values dynamically for IN clause?
TIA.

Two caveats:
1) I have not tested the SQL below, so I can't guarantee no syntax errors.
2) I do this a lot with PIVOT, but am not familiar with UNPIVOT. Assuming they work the same (the syntax looks the same), then this should work.

OK, enough of that. Here's my response:
Yes. You will need to use coalesce. As in:

DECLARE @PivotColumnHeaders VARCHAR(MAX)
DECLARE @PivotTableSQL NVARCHAR(MAX)

SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ', [' + RTRIM(HeadersColName) + ']',
'[' + RTRIM(HeadersColName) + ']'
)
FROM (
SELECT DISTINCT HeadersColName
FROM (TempTable TempTable
-- Insert Joins and SQL here
WHERE (MyCol = @Constraint)
) AS t

SET @PivotTableSQL = N'
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS Rn,* FROM Table) t
UNPIVOT (Value FOR Column IN (' + @PivotColumnHeaders + '))u
'

--PRINT(@PivotTableSQL) --Uncomment for editing

EXECUTE(@PivotTableSQL)

FischMan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:26:38

quote:
Originally posted by learntsql

Hi Visakhm,

Is this possible to get the values dynamically for IN clause?
TIA.

you can but you need to use dynamic sql

see similar scenario used for PIVOT in below link

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-04 : 14:07:06

quote:
Originally posted by visakh16
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

That seems more complicated and opaque than simply doing a coalesce on the column data you want to (un)pivot on.

FischMan

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-04 : 23:11:33

All that you guys mentioning the column names as col1 and col2.
But here I doesnt know the column names.
It might be varied each and every time. Thats why I have specified that its n number of columns.

All the above answers suits only If Column names is known.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 10:25:22

quote:
Originally posted by FischMan2

quote:
Originally posted by visakh16
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

That seems more complicated and opaque than simply doing a coalesce on the column data you want to (un)pivot on.

FischMan

but unless you know the values to be unpivoted before hand how can avoid dynamic sql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-05 : 11:56:18

quote:
Originally posted by Kalaiselvan

All that you guys mentioning the column names as col1 and col2.
But here I doesnt know the column names.
It might be varied each and every time. Thats why I have specified that its n number of columns.

All the above answers suits only If Column names is known.

Regards,
Kalaiselvan R
Love Yourself First....

quote:

but unless you know the values to be unpivoted before hand how can avoid dynamic sql?

The COALESCE statement generates the random number of columns, and generates the random column names. When my pivot queries are run, the resulting columns could be one or, literally, several hundred. The coalesce statement handles all of this nicely and neatly. Unless I am missing something here, it should work.

The only place I use "col1" is in the "ORDER BY" clause of the ROW_NUMBER() function. You will need to provide something there to order by. What it is, is up to you. There should be some column you can use to order by. I suppose, worst case scenario, you could throw in your header columns. Or maybe create a new variable (@OrderByCols) and populate it based on a separate COALESCE query.

If, by "dynamic SQL," you mean generating the SQL on the fly as the query is run, you'll note that I am generating dynamic SQL.

Hands shaking. Too much coffee.

FischMan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources