Urgent requirement for Pivoting 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 2012 Forums

Transact-SQL (2012)

Urgent requirement for Pivoting table

Author

Topic

amit120k
Starting Member

16 Posts

Posted - 2013-11-01 : 06:50:36

i have table like with a value like this:

15-06-2013 30-07-2013 31-07-2013 01-08-2013
---------- ---------- ---------- ----------
NULL 1 2 3
2 3 NULL NULL
1 NULL 4 5

The pivot table will look like this:

15-06-2013 NULL 2 1
30-07-2013 1 3 NULL
31-07-2013 2 NULL 4
01-08-2013 3 NULL 5

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-11-01 : 07:50:39

Provide Table Structure

veeranjaneyulu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-01 : 08:08:46

check out http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

KH
[spoiler]Time is always against us[/spoiler]

amit120k
Starting Member

16 Posts

Posted - 2013-11-01 : 10:05:32

quote:
Originally posted by amit120k

i have table like with a value like this:

15-06-2013 30-07-2013 31-07-2013 01-08-2013
---------- ---------- ---------- ----------
NULL 1 2 3
2 3 NULL NULL
1 NULL 4 5

The pivot table will look like this:

15-06-2013 NULL 2 1
30-07-2013 1 3 NULL
31-07-2013 2 NULL 4
01-08-2013 3 NULL 5

amit120k
Starting Member

16 Posts

Posted - 2013-11-04 : 01:46:15

i have table with a value like this:

15-06-2013 30-07-2013 31-07-2013 01-08-2013
---------- ---------- ---------- ----------
NULL 1 2 3
2 3 NULL NULL
1 NULL 4 5

I need to convert the rows into columns and corresponding value will display against that:

15-06-2013 NULL 2 1
30-07-2013 1 3 NULL
31-07-2013 2 NULL 4
01-08-2013 3 NULL 5

[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 02:43:09

something like this


DECLARE @ColList varchar(max),@SQL varchar(max)

SELECT @ColList = STUFF((SELECT ',[' + COLUMN_NAME  + ']' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customers' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM
(
SELECT RN,DateVal,Val
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN,* FROM TableName)t
UNPIVOT(Val FOR DateVal IN (' + @ColList + '))u
)m
PIVOT(MAX(Val) FOR RN IN ([1],[2],[3]))p'

EXEC (@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources