Dates to Column - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Dates to Column

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-11-25 : 08:04:37

hi

I would like to convert dates in rows to columns and the dates are dynamics. Here is my sample data and expected result set. Thanks a lot.

Sample Data

A 2013-08-12
A 2013-08-13
A 2013-08-14
A 2013-08-15
A 2013-08-16
B 2013-08-12
B 2013-08-13
B 2013-08-14
B 2013-08-15
B 2013-08-16
C 2013-08-12
C 2013-08-13
C 2013-08-14
C 2013-08-15
C 2013-08-16

Expected Results

2013-08-12 2013-08-13 2013-08-14 2013-08-15 2013-08-16
A
B
C

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 09:34:30

something like below


DECLARE @DAteList varchar(5000),@SQL varchar(max)

SELECT @DAteList =STUFF ((SELECT DISTINCT ',[' + CONVERT(varchar(100),DatefIeld,112) + ']'
                  FROM Table
                  ORDER BY ',[' + CONVERT(varchar(100),DatefIeld,112) + ']'
                  FOR XML PATH('')),1,1'')

SET @SQL='SELECT *
 FROM Table t
 PIVOT (MAX(1) FOR DateField IN (' + @DateList + '))p'
EXEC(@SQL)

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

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-11-25 : 10:09:54

hi

My View_2 consist of 2 fields, CustName and Dates.

I tried using this

DECLARE @DAteList varchar(5000), @SQL varchar(max)

SELECT @DAteList = STUFF ((SELECT DISTINCT Dates
FROM View_2
ORDER BY Dates
FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM View_2 t
PIVOT (MAX(1) FOR Dates IN (' + @DateList + '))p'
EXEC(@SQL)

I received this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '1'.

I could not figure out how to solve this error. Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 13:30:39

Please try to use as suggested


DECLARE @DAteList varchar(5000), @SQL varchar(max)

SELECT @DAteList = STUFF ((SELECT DISTINCT ',[' + Dates + ']'
FROM View_2
ORDER BY Dates
FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM View_2 t
PIVOT (MAX(1) FOR Dates IN (' + @DateList + '))p'
EXEC(@SQL)

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

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-11-26 : 00:18:40

hi

I have now add a where clause in both query using @BatchID as a variable which is a GUID in my view_2. the error I got which I think is cause by the Set @SQL for pivot in bold. The is the error message"

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'F74FC'.

How should I go about it? Thanks a lot

One more thing, without the where clause everything is fine. Thanks.

Here is the Code which I tried executing:

DECLARE @DateList varchar(5000), @SQL varchar(max), @batchid uniqueidentifier

set @batchid = '460f74fc-c318-4152-b758-4afa1db9c890'

SELECT @DateList = STUFF((SELECT DISTINCT ',[' + Dates + ']'
FROM View_2 where View_2.BatchID = @batchid
ORDER BY ',[' + Dates + ']'
FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM View_2 t where t.batchid = '+ cast(@batchid as varchar(100))+ ')
PIVOT (Max(empty) FOR Dates IN (' + @DateList + '))p'

EXEC(@SQL)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-26 : 01:32:06

do a PRINT @SQL before the EXEC statement. You will be able to spot the error

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

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-11-26 : 02:29:59

Thanks visakh16 and KHTan

The correct one is this:

DECLARE @DateList varchar(5000), @SQL varchar(max), @batchid uniqueidentifier

set @batchid = '460f74fc-c318-4152-b758-4afa1db9c890'

SELECT @DateList = STUFF((SELECT DISTINCT ',[' + Dates + ']'
FROM View_2 where View_2.BatchID = @batchid
ORDER BY ',[' + Dates + ']'
FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM View_2 t
PIVOT (Max(t.empty) FOR t.Dates IN (' + @DateList + '))p
where p.batchid = '''+ cast(@batchid as varchar(100))+ ''''

EXEC(@SQL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 05:30:10

Glad that you got it sorted

------------------------------------------------------------------------------------------------------
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