Solved

Dynamic Crosstab query in SQL Server 2005

Posted on 2010-09-10
6
463 Views
Last Modified: 2012-05-10
I'm trying to create a dynamic crosstab query in server 2005. I copied this codes from a book and modified it to my purpose, but I couldn't get it to work. I got the error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. My crosstab columns is a datetime field. Here's is the codes, please help:

DECLARE @XColumns NVARCHAR(1024)
SET @XColumns = ''

SELECT @XColumns = @XColumns + [a].[Column] + ', '
      FROM
            (SELECT DISTINCT [Date] AS [Column]
                  FROM TRP.CAP_PHID) AS a

PRINT @XColumns
SET @XColumns = Left(@XColumns, LEN(@XColumns) - 1)

SET @XColumns = 'SELECT Tab, PhID, ProdLine, Segment, '
      + @XColumns
      + ' FROM TRP.CAP_PHID PIVOT (SUM (FullProj) FOR [Date] IN ('
      + @XColumns
      + ')) AS pt'
PRINT @XColumns

EXEC sp_executesql @XColumns
0
Comment
Question by:HNA071252
  • 3
  • 2
6 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33649813
may I suggest this code instead?

DECLARE @strSQL VARCHAR(MAX) 
DECLARE @COLS NVARCHAR(2000) 
  
SELECT @COLS = STUFF((SELECT  distinct
                                        '], [' + convert(varchar, [Date], 112)
                                FROM TRP.CAP_PHID order by 1
                                FOR XML PATH('') 
                                ), 1, 2, '') + ']' 


set @strSQL = 'SELECT Tab, PhID, ProdLine, Segment, ' + @Cols
      + ' FROM TRP.CAP_PHID 
	  PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + ')) AS pt'


EXEC sp_executesql @strSQL

Open in new window

0
 

Expert Comment

by:A071252
ID: 33650278
How do I use this code? I executed in the SQL Server window and I got this message:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 33650354
just add N' there

DECLARE @strSQL NVARCHAR(MAX) 
DECLARE @COLS NVARCHAR(2000) 
  
SELECT @COLS = STUFF((SELECT  distinct
                                        '], [' + convert(varchar, [Date], 112)
                                FROM TRP.CAP_PHID order by 1
                                FOR XML PATH('') 
                                ), 1, 2, '') + ']' 


set @strSQL = N'SELECT Tab, PhID, ProdLine, Segment, ' + @Cols
      + N' FROM TRP.CAP_PHID 
	  PIVOT (SUM (FullProj) FOR [Date] IN (' + @Cols + N')) AS pt'


EXEC sp_executesql @strSQL

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Expert Comment

by:A071252
ID: 33650434
Great! It works. How do I change to show the column as "200901" (instead of 20090101, etc.)? Also how do I put this into a view so that I could link to it from Access?
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 500 total points
ID: 33650507
>>Also how do I put this into a view so that I could link to it from Access? <<
you can't.
What you will need to to is create a stored procedure and call the SP from access.
>>How do I change to show the column as "200901" (instead of 20090101, etc.)?  <<
So you just want to show the month and not all days? Try the below:

DECLARE @strSQL NVARCHAR(MAX) 
DECLARE @COLS NVARCHAR(2000) 
  
SELECT @COLS = STUFF((SELECT  distinct
                                        '], [' + convert(varchar(6), [Date], 112)
                                FROM TRP.CAP_PHID order by 1
                                FOR XML PATH('') 
                                ), 1, 2, '') + ']' 


set @strSQL = N'SELECT Tab, PhID, ProdLine, Segment, ' + @Cols
      + N' FROM (SELECT *, convert(varchar(6), [Date]) as dat TRP.CAP_PHID ) o
	  PIVOT (SUM (FullProj) FOR dat IN (' + @Cols + N')) AS pt'


EXEC sp_executesql @strSQL

Open in new window

0
 

Author Closing Comment

by:HNA071252
ID: 33650717
Great help, Thank you very much!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Computers reporting Windows patches applied 14 106
How to simplify my SQL statement? 14 54
IT Desktop Support 11 75
How can i use WITH CTE for checking exist value? 3 34
Microsoft has released remote PowerShell capabilities to all commercial Office 365 customers. So you can be controlled via PowerShell and not from the Office 365 admin center Download Windows PowerShell Module for Lync Online http://www.micros…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
Viewers will learn the different options available in the Backstage view in Excel 2013.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question