[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

simple PIVOT with 1 table

I have a table that contains columns and rows as explained below

Table1:
col1, col1, col3 ... colN
------------------------------
val1, val2, val3 ... valN

I want to run a SELECT that will pivot the result as shows below

column1, column2
-----------------
col1   , val1
col2   , val2
col3   , val3
...
colN   , valN

* Number of columns in the table is dynamic.
* Select will always select only one row from the table.
0
vu3lmg
Asked:
vu3lmg
  • 3
  • 3
1 Solution
 
penyCuicasCommented:
you can do this:


DECLARE  @numberOfColumns            INT
            ,@columnaName                  VARCHAR(10)
            ,@sqlText                        VARCHAR(3000)
SELECT   @numberOfColumns      = 3
            ,@columnaName            = 'col'
            ,@sqlText      = '';
--* Create de secuence
WITH cteSecuence (SeqNo) AS
(
        SELECT 1
        UNION ALL
        SELECT SeqNo + 1                   
        FROM cteSecuence
        WHERE SeqNo < @numberOfColumns
)
SELECT @sqlText = @sqlText + CASE WHEN @sqlText != '' THEN CHAR(13) + ' UNION ALL ' ELSE '' END + 'SELECT TOP 1 ''' + @columnaName + CONVERT(VARCHAR ,SeqNo) + ''' ,' + @columnaName + CONVERT(VARCHAR ,SeqNo) + ' FROM MyTable '  FROM cteSecuence
EXEC (@sqlText)
0
 
vu3lmgAuthor Commented:
col1, col2 are just examples, actual table will have real column names, they do not follow any sequence.
0
 
ralmadaCommented:
try


declare @cols varchar(max)
declare @strSQL varchar(max)

set @cols = stuff((select ',' + name	from sys.columns where id = object_id('yourtable') for xml path('')), 1, 1, '')


set @strSQL = '	SELECT 	Property, Value 
		FROM yourtable
		UNPIVOT (Value For Property In (' + @cols + ')) as tblPivot'


exec(@strSQL)

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
ralmadaCommented:
or like this if the columns have different datatypes




declare @cols varchar(max)
declare @strSQL varchar(max)

set @cols = stuff((select ',' + name	from sys.columns where id = object_id('yourtable') for xml path('')), 1, 1, '')


set @strSQL = '	SELECT 	Property, Value 
		FROM (
		select convert(variant, col1) as col1,
			convert(variant, col2) as col2,
			convert(variant, col3) as col3,
			... and so on ...
		from yourtable
		) o
		UNPIVOT (Value For Property In (' + @cols + ')) as tblPivot'


exec(@strSQL)

Open in new window

0
 
vu3lmgAuthor Commented:
ralmada,
yes the columns have different datatypes,  and as mentioned in the question, number of columns is also dynamic to be decided at the time of SQL execution.
I am trying, but unsuccessful in modifying your example for the above mentioned situation (dynamic number of columns and their data types are different)
Please help.
0
 
ralmadaCommented:
try


declare @cols varchar(max)
declare @col1 varchar(max)
declare @strSQL varchar(max)

set @cols = stuff((select ',' + name	from sys.columns where id = object_id('yourtable') for xml path('')), 1, 1, '')

set @col1 = stuff((select ', convert(variant, ' + name	+ ') as ' + name from sys.columns where id = object_id('yourtable') for xml path('')), 1, 1, '')


set @strSQL = '	SELECT 	Property, Value 
		FROM (
		select ' + @col1 + ' from yourtable
		) o
		UNPIVOT (Value For Property In (' + @cols + ')) as tblPivot'


exec(@strSQL)

Open in new window

0
 
vu3lmgAuthor Commented:
ralmada,
I tried improvizing and comeup with exactly what you've suggested.
You'll get the points.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now