January 28, 2011 at 6:35 am
Hi please help for this
am having a table like this
Name
------
K
A
S
I
N
A
T
-----
i need to create a tamp table like this ( rows are as colums in dynamic)
temp table :
---------------------------
K | A | S | I | N | A | T
---------------------------
Please help me
January 28, 2011 at 9:49 am
Here's a generic dynamic sql statement to generate your pivot table. Keep in mind that if the list of distinct names is long, this will take a while to generate (i.e. don't run this on large tables)
create table #t1 (name varchar(20))
insert into #t1
select 'K' union all
select 'A' union all
select 'S' union all
select 'I' union all
select 'N' union all
select 'A' union all
select 'T'
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT Name
FROM #t1
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT a.name
FROM #t1 a
) t
PIVOT (min(Name) FOR Name IN (' + @ColsList + ')) PVT')
For better, quicker answers, 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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply