Brackets along with table Header strings

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)

Brackets along with table Header strings

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-09-05 : 12:37:03

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-05 : 13:05:19

[code]SELECT SpatialReportID, CustomerCare as "[CustomerCare]", CustomerNumber as "[CustomerNumber]"
FROM ( SELECT SpatialReportID, AnalysisDescription , ResultDescription
.....[/code]

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-09-05 : 15:16:43

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-05 : 17:24:29

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-09-06 : 15:08:32

Thank you James for your response..but i am getting incorrect syntax
near the keyword 'as' erorr..Can you please help me with that..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 15:53:15

Change the "exec(@query);" to "print @query" and post the result it prints out when you execute it.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-09-06 : 16:37:31

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 ;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 16:59:33

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

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2013-09-06 : 17:08:26

Thank You james just worked perfect..

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-06 : 17:43:48

you are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources