Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

akpaga
Constraint Violating Yak Guru

USA
331 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

3873 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
331 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

3873 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
331 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

3873 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
331 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

3873 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
331 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

3873 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  
 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.11 seconds. Powered By: Snitz Forums 2000