SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Urgent requirement for Pivoting table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amit120k
Starting Member

India
14 Posts

Posted - 11/01/2013 :  06:50:36  Show Profile  Reply with Quote
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

India
169 Posts

Posted - 11/01/2013 :  07:50:39  Show Profile  Reply with Quote
Provide Table Structure

veeranjaneyulu
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 11/01/2013 :  08:08:46  Show Profile  Reply with Quote
check out http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx


KH
Time is always against us

Go to Top of Page

amit120k
Starting Member

India
14 Posts

Posted - 11/01/2013 :  10:05:32  Show Profile  Reply with Quote
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



Go to Top of Page

amit120k
Starting Member

India
14 Posts

Posted - 11/04/2013 :  01:46:15  Show Profile  Reply with Quote
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]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/04/2013 :  02:43:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000