Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1873
Members Online:
0
Guests Online:
36

Total Post History
Posts:
81445
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
BitogClogs

Other

FAQ
All times are UTC [ DST ]

Google Ads

brackets for dynamic column headers

Microsoft SQL Server
Please wait...

brackets for dynamic column headers

Postby ravidora2004 on Thu Sep 05, 2013 9:17 pm

Using the below code i pivot my results in the query but what i want now is the
in the results of this sp the column headers should have brackets around them
like [Customer Care],[Customer Address]
The specific reason why i am asking is that i am getting missing operand before operator error when using this query for reporting in the front end because the column headers is having spaces between them

Thank you in advance

  1. ALTER PROCEDURE [dbo].[SpatialQueryReport_sp]
  2. @SpatialIdDynamic AS NVARCHAR(MAX) = null,
  3. @SpatialId AS NVARCHAR(MAX) =null
  4. AS
  5. SET NOCOUNT ON
  6. BEGIN
  7. DECLARE @cols AS NVARCHAR(MAX);
  8. DECLARE @query AS NVARCHAR(MAX);
  9. --DECLARE @SpatialId AS NVARCHAR(MAX);
  10. --DECLARE @SpatialIdDynamic AS NVARCHAR(MAX);
  11.  
  12. select @cols = STUFF((SELECT DISTINCT ',' +
  13. QUOTENAME(AnalysisDescription)
  14. FROM Customer.CustomerAdd
  15. where Convert(uniqueidentifier,@SpatialId) = SpatialReportID
  16. FOR XML PATH(''), TYPE
  17. ).value('.', 'NVARCHAR(MAX)')
  18. , 1, 1, '');
  19.  
  20. SELECT @query =
  21.  
  22. 'SELECT *
  23. FROM
  24. (
  25. SELECT
  26. SpatialReportID,
  27. AnalysisDescription,
  28. ResultDescription
  29. FROM Customer.CustomerAdd
  30. WHERE Convert(uniqueidentifier,'''+@SpatialIdDynamic +''') = SpatialReportID
  31.  
  32. ) AS t
  33. PIVOT
  34. (
  35. MAX(ResultDescription)
  36. FOR AnalysisDescription IN( ' + @cols + ' )' +
  37. ' ) AS p ; ';
  38.  
  39. exec(@query);
  40. end
ravidora2004
Newbie
Newbie
 
Posts: 2
Joined: Thu Feb 28, 2013 11:17 pm
Unrated