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.
LVL 1
vu3lmgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vu3lmgAuthor Commented:
ralmada,
I tried improvizing and comeup with exactly what you've suggested.
You'll get the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.