Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic Crosstab query in SQL Server 2005

Posted on 2010-09-10
6
Medium Priority
?
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 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 2000 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

722 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