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 2008 Forums
 Transact-SQL (2008)
 Brackets along with table Header strings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 09/05/2013 :  12:37:03  Show Profile  Reply with Quote
Hi friends in the result of the query below i would like to show the headers with brackets like
[CustomerCare],[Customernumber]
regular , 12345

How can i achieve it..

SELECT * FROM ( SELECT SpatialReportID, AnalysisDescription , ResultDescription
FROM Customer.vwSpatialReport
WHERE Convert(uniqueidentifier,'advsffdd') = SpatialReportID) AS t
PIVOT
( MAX(ResultDescription)
FOR AnalysisDescription IN( [CustomerCare], [CustomerNumber] ) ) AS p

Edited by - akpaga on 09/05/2013 12:37:19

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/05/2013 :  13:05:19  Show Profile  Reply with Quote
SELECT SpatialReportID, CustomerCare as "[CustomerCare]", CustomerNumber as "[CustomerNumber]"
FROM  ( SELECT SpatialReportID, AnalysisDescription , ResultDescription 
.....
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 09/05/2013 :  15:16:43  Show Profile  Reply with Quote
Thank You james. Got that resolved there but but i want your help in doing the same i.e brackets for headers using this dynamic query here

ALTER PROCEDURE [dbo].[SpatialQueryReport_sp]
@SpatialIdDynamic AS NVARCHAR(MAX) = null,
@SpatialId AS NVARCHAR(MAX) =null
AS
SET NOCOUNT ON
BEGIN
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
--DECLARE @SpatialId AS NVARCHAR(MAX);
--DECLARE @SpatialIdDynamic AS NVARCHAR(MAX);
--set @SpatialId ='54CB783F-2207-45BE-A617-E730AD7D21C5';
--set @SpatialIdDynamic ='54CB783F-2207-45BE-A617-E730AD7D21C5'
select @cols = STUFF((SELECT DISTINCT ',' +
QUOTENAME(AnalysisDescription)
FROM GISportal.vwSpatialReport
where Convert(uniqueidentifier,@SpatialId) = SpatialReportID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
SELECT
SpatialReportID,
AnalysisDescription,
ResultDescription
FROM GISportal.vwSpatialReport
WHERE Convert(uniqueidentifier,'''+@SpatialIdDynamic +''') = SpatialReportID

) AS t
PIVOT
(
MAX(ResultDescription)
FOR AnalysisDescription IN( ' + @cols + ' )' +
' ) AS p ; ';

exec(@query);
end
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/05/2013 :  17:24:29  Show Profile  Reply with Quote
Replace this
....
SELECT @query =

'SELECT *
FROM
....
with this:

......
DECLARE @cols2 AS NVARCHAR(MAX);
select @cols2 = STUFF((SELECT DISTINCT ',' +
QUOTENAME(AnalysisDescription) + ' as "' + QUOTENAME(AnalysisDescription) + '"'
FROM GISportal.vwSpatialReport
where Convert(uniqueidentifier,@SpatialId) = SpatialReportID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
, 1, 1, '');

SELECT @query =

'SELECT SpatialReportID, ' + @cols2 + ' ' + '
FROM
....
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 09/06/2013 :  15:08:32  Show Profile  Reply with Quote
Thank you James for your response..but i am getting incorrect syntax
near the keyword 'as' erorr..Can you please help me with that..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/06/2013 :  15:53:15  Show Profile  Reply with Quote
Change the "exec(@query);" to "print @query" and post the result it prints out when you execute it.
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 09/06/2013 :  16:37:31  Show Profile  Reply with Quote
Hee it is ..Thank you

SELECT SpatialReportID, [CDM Historic Designation] as " [CDM Historic Designation]",[Does Area Have a Poverty Level > 40%] as " [Does Area Have a Poverty Level > 40%]",[Galveston Historic Foundation Legal Covenant] as " [Galveston Historic Foundation Legal Covenant]",[Historic Designation] as " [Historic Designation]",[Is Area a Galveston Landmark] as " [Is Area a Galveston Landmark]",[Is Area a National Register Property] as " [Is Area a National Register Property]",[Is Area within 150' of a Landmark] as " [Is Area within 150' of a Landmark]",[Is Area within a National Register Historic District?] as " [Is Area within a National Register Historic District?]",[Is Area within a Potential NRHP-Eligible District] as " [Is Area within a Potential NRHP-Eligible District]",[Target Area ID] as " [Target Area ID]",[Target Area Level] as " [Target Area Level]"
FROM
(
SELECT
SpatialReportID,
AnalysisDescription,
ResultDescription
FROM vwSpatialReport
WHERE Convert(uniqueidentifier,'54CB783F-2207-45BE-A617-E730AD7D21C5') = SpatialReportID

) AS t
PIVOT
(
MAX(ResultDescription)
FOR AnalysisDescription IN( [CDM Historic Designation] as " [CDM Historic Designation]",[Does Area Have a Poverty Level > 40%] as " [Does Area Have a Poverty Level > 40%]",[Galveston Historic Foundation Legal Covenant] as " [Galveston Historic Foundation Legal Covenant]",[Historic Designation] as " [Historic Designation]",[Is Area a Galveston Landmark] as " [Is Area a Galveston Landmark]",[Is Area a National Register Property] as " [Is Area a National Register Property]",[Is Area within 150' of a Landmark] as " [Is Area within 150' of a Landmark]",[Is Area within a National Register Historic District?] as " [Is Area within a National Register Historic District?]",[Is Area within a Potential NRHP-Eligible District] as " [Is Area within a Potential NRHP-Eligible District]",[Target Area ID] as " [Target Area ID]",[Target Area Level] as " [Target Area Level]" ) ) AS p ;
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/06/2013 :  16:59:33  Show Profile  Reply with Quote
Is this the query you are using? If not, use this:
ALTER PROCEDURE [dbo].[SpatialQueryReport_sp]
@SpatialIdDynamic AS NVARCHAR(MAX) = null, 
@SpatialId AS NVARCHAR(MAX) =null
AS
SET NOCOUNT ON
BEGIN
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
--DECLARE @SpatialId AS NVARCHAR(MAX);
--DECLARE @SpatialIdDynamic AS NVARCHAR(MAX);
--set @SpatialId ='54CB783F-2207-45BE-A617-E730AD7D21C5';
--set @SpatialIdDynamic ='54CB783F-2207-45BE-A617-E730AD7D21C5'
select @cols = STUFF((SELECT DISTINCT ',' +
QUOTENAME(AnalysisDescription) 
FROM GISportal.vwSpatialReport
where Convert(uniqueidentifier,@SpatialId) = SpatialReportID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
, 1, 1, '');

DECLARE @cols2 AS NVARCHAR(MAX);
select @cols2 = STUFF((SELECT DISTINCT ',' +
QUOTENAME(AnalysisDescription) + ' as "' + QUOTENAME(AnalysisDescription) + '"'
FROM GISportal.vwSpatialReport
where Convert(uniqueidentifier,@SpatialId) = SpatialReportID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
, 1, 1, '');

SELECT @query =

'SELECT SpatialReportID, ' + @cols2 + ' ' + '
FROM

(
SELECT
SpatialReportID,
AnalysisDescription,
ResultDescription
FROM GISportal.vwSpatialReport 
WHERE Convert(uniqueidentifier,'''+@SpatialIdDynamic +''') = SpatialReportID

) AS t
PIVOT 
(
MAX(ResultDescription) 
FOR AnalysisDescription IN( ' + @cols + ' )' +
' ) AS p ; ';

exec(@query); 
end
Go to Top of Page

akpaga
Constraint Violating Yak Guru

USA
310 Posts

Posted - 09/06/2013 :  17:08:26  Show Profile  Reply with Quote
Thank You james just worked perfect..
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 09/06/2013 :  17:43:48  Show Profile  Reply with Quote
you are very welcome - glad to help.
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.14 seconds. Powered By: Snitz Forums 2000