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 2012 Forums
 Transact-SQL (2012)
 Help returning data columns as a row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

donkyho
Starting Member

Canada
12 Posts

Posted - 06/12/2013 :  10:03:29  Show Profile  Reply with Quote
I have a stored procedure that gets me the data I need, however I am wondering if there's a way I can get this data to return in different format.

Here's an example of the return data from my SP



Notice that the first 4 columns return the exact same data.

Basically I'd like all this data returned in 1 column. With the 2nd last column values (Region) as the column names and the 'Visible' column as the value.

For example, this is how I would like this data returned



Is there any way to achieve this?

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/12/2013 :  10:08:50  Show Profile  Reply with Quote
You have to PIVOT query for this kind of result

1) create table (Ex: YourTable) with the above columns which are coming from SP
2) INSERT INTO YourTable
EXEC YourProcedure
3) PIVOT Query by using table "YourTable"
Follow this link for PIVOT Query
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186015
SELECT

FROM

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/12/2013 :  10:11:07  Show Profile  Reply with Quote
you can use pivot for thie

first drop the resultset of sp to temporary table

INSERT #Temp
EXEC yourprocedurename ....

the table #Temp should have same structure as your procedure resultset

then use query like this


SELECT *
FROM #Temp t
PIVOT(MAX(Visible FOR Region IN ([London and Middlesex],[Oxford and Norfolk],[Elgin],[Huron and Perth],[Grey and Bruce]))p


if you want to make it dynamic use


DECLARE @RegionList varchar(2000),@SQL varchar(max)

SELECT @RegionList = STUFF((SELECT DISTINCT ',[' + Region + ']' FROM #Temp ORDER BY ',[' + Region + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM #Temp t
PIVOT(MAX(Visible FOR Region IN (' + @RegionList + '))p'

EXEC(@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

donkyho
Starting Member

Canada
12 Posts

Posted - 06/12/2013 :  11:01:28  Show Profile  Reply with Quote
Thanks for the response. I'm trying what you say, but I still have some problems. I'm getting an 'Incorrect syntax near the keyword 'FOR' erro and I'm not sure why. Here's my full script

ALTER PROCEDURE sp_RegionTabDashboard_Get
(
@ServiceRecordID INT
)
AS

CREATE TABLE #Temp
(
ServiceRecordID INT,
LocationStd VARCHAR(1000),
AreaServedStd VARCHAR(1000),
RegionalLimited BIT,
Region VARCHAR(255),
Visible BIT
)

INSERT INTO #Temp
SELECT SR.ServiceRecordID, SR.LocationStd, SR.AreaServedStd, SR.RegionalLimited, R.Region,
CASE WHEN (ISNULL(R_SR.RegionID,0) = 0 AND ISNULL(R_SR_Serv.RegionID,0) = 0) THEN 0 ELSE 1 END AS Visible
FROM ServiceRecord SR
INNER JOIN Sites S ON SR.SiteID = S.SiteID
INNER JOIN Regions R ON R.SiteID = S.SiteID
LEFT OUTER JOIN lkup_Region_ServiceRecord R_SR ON R_SR.RegionID = R.RegionID AND R_SR.ServiceRecordID = SR.ServiceRecordID
LEFT OUTER JOIN lkup_Region_ServiceRecord_Serv R_SR_Serv ON R_SR_Serv.RegionID = R.RegionID AND R_SR_Serv.ServiceRecordID = SR.ServiceRecordID
WHERE SR.ServiceRecordID = @ServiceRecordID
AND R.RegionID % 100 != 0


DECLARE @RegionList varchar(2000),@SQL varchar(max)

SELECT @RegionList = STUFF((SELECT DISTINCT ',[' + Region + ']' FROM #Temp ORDER BY ',[' + Region + ']' FOR XML PATH('')),1,1,'')


SET @SQL='SELECT * FROM #Temp PIVOT(MAX(Visible) FOR Region IN (' + @RegionList + '))'

EXEC(@SQL)

Edited by - donkyho on 06/12/2013 11:17:09
Go to Top of Page

donkyho
Starting Member

Canada
12 Posts

Posted - 06/12/2013 :  11:28:02  Show Profile  Reply with Quote
Got it. Had to make 'Visible' an INT field in the temp table in order to use the MAX() function around it in the pivot.

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/12/2013 :  11:35:01  Show Profile  Reply with Quote
cool

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/12/2013 :  11:51:17  Show Profile  Reply with Quote
Try modifying you SQL statement as below:

quote:
Originally posted by donkyho

Thanks for the response. I'm trying what you say, but I still have some problems. I'm getting an 'Incorrect syntax near the keyword 'FOR' erro and I'm not sure why. Here's my full script

ALTER PROCEDURE sp_RegionTabDashboard_Get
(
@ServiceRecordID INT
)
AS

CREATE TABLE #Temp
(
ServiceRecordID INT,
LocationStd VARCHAR(1000),
AreaServedStd VARCHAR(1000),
RegionalLimited BIT,
Region VARCHAR(255),
Visible BIT
)

INSERT INTO #Temp
SELECT SR.ServiceRecordID, SR.LocationStd, SR.AreaServedStd, SR.RegionalLimited, R.Region,
CASE WHEN (ISNULL(R_SR.RegionID,0) = 0 AND ISNULL(R_SR_Serv.RegionID,0) = 0) THEN 0 ELSE 1 END AS Visible
FROM ServiceRecord SR
INNER JOIN Sites S ON SR.SiteID = S.SiteID
INNER JOIN Regions R ON R.SiteID = S.SiteID
LEFT OUTER JOIN lkup_Region_ServiceRecord R_SR ON R_SR.RegionID = R.RegionID AND R_SR.ServiceRecordID = SR.ServiceRecordID
LEFT OUTER JOIN lkup_Region_ServiceRecord_Serv R_SR_Serv ON R_SR_Serv.RegionID = R.RegionID AND R_SR_Serv.ServiceRecordID = SR.ServiceRecordID
WHERE SR.ServiceRecordID = @ServiceRecordID
AND R.RegionID % 100 != 0


DECLARE @RegionList varchar(2000),@SQL varchar(max)

SELECT @RegionList = STUFF((SELECT DISTINCT ',[' + Region + ']' FROM #Temp ORDER BY ',[' + Region + ']' FOR XML PATH('')),1,1,'')


SET @SQL='SELECT * FROM #Temp PIVOT(MAX(Visible) FOR Region IN (' + @RegionList + '))'

SET @SQL='SELECT * FROM
(SELECT ServiceRecordID,
LocationStd,
AreaServedStd,
RegionalLimited,
Region As Region,
CAST(Visible AS INT) AS Visible FROM #Temp) B PIVOT(MAX(Visible) FOR Region IN (' + @RegionList + ')) A'


EXEC(@SQL)

Go to Top of Page

donkyho
Starting Member

Canada
12 Posts

Posted - 06/13/2013 :  10:21:23  Show Profile  Reply with Quote
Thanks for the help, it all works almost perfectly. However, there's still 1 minor problem. For example, if I have 5 Regions total in the current location, I need all 5 regions to come back in the regions columns, and Visible=1 or 0 for each record under each region. Because of the complexity of the procedure, and the fact nearly 6,000 rows return, it takes quite a long time. Because of this, I tried adding a 'Top 100' to the original SELECT statement, but when I did that, I only got 1 of the 5 regions back in the pivot table. Here's my script


CREATE TABLE #Temp
(
ServiceRecordID INT,
ServiceName VARCHAR(MAX),
LocationStd VARCHAR(1000),
AreaServedStd VARCHAR(1000),
RegionalLimited BIT,
Region VARCHAR(255),
Visible BIT,
OtherSites VARCHAR(1000)
)

INSERT INTO #Temp
SELECT TOP 100 SR.ServiceRecordID, dbo.fn_ServiceRecordGetServiceName(SR.ServiceRecordID,''), SR.LocationStd, SR.AreaServedStd, SR.RegionalLimited, R.Region,
CASE WHEN (ISNULL(R_SR.RegionID,0) = 0 AND ISNULL(R_SR_Serv.RegionID,0) = 0) THEN 0 ELSE 1 END AS Visible,dbo.fn_GetOtherRegionalSitesForServiceRecord(SR.ServiceRecordID)
FROM ServiceRecord SR
INNER JOIN Sites S ON SR.SiteID = S.SiteID
INNER JOIN Regions R ON R.SiteID = S.SiteID
LEFT OUTER JOIN lkup_Region_ServiceRecord R_SR ON R_SR.RegionID = R.RegionID AND R_SR.ServiceRecordID = SR.ServiceRecordID
LEFT OUTER JOIN lkup_Region_ServiceRecord_Serv R_SR_Serv ON R_SR_Serv.RegionID = R.RegionID AND R_SR_Serv.ServiceRecordID = SR.ServiceRecordID AND SR.RegionalLimited = 0
WHERE SR.SiteID = @SiteID
AND R.RegionID % 100 != 0

DECLARE @RegionList varchar(2000),@SQL varchar(max)
SELECT @RegionList = STUFF((SELECT DISTINCT ',[' + Region + ']' FROM #Temp ORDER BY ',[' + Region + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT * FROM
(SELECT ServiceRecordID,
LocationStd,
AreaServedStd,
RegionalLimited,
Region As Region,
CAST(Visible AS INT) AS Visible FROM #Temp) B PIVOT(MAX(Visible) FOR Region IN (' + @RegionList + ')) A'

EXEC(@SQL)
Go to Top of Page

donkyho
Starting Member

Canada
12 Posts

Posted - 06/13/2013 :  10:36:23  Show Profile  Reply with Quote
I think I figured it out.

adding 'ORDER BY SR.ServiceRecordID' to the main SELECT fixed it
Go to Top of Page

donkyho
Starting Member

Canada
12 Posts

Posted - 06/13/2013 :  12:17:42  Show Profile  Reply with Quote
One last question.

How can I run the function calls AFTER the pivot rather than in the initial select? The reason for the query taking so long is because the initial SELECT is returning nearly 30,000 rows, and the 2 functions are running 30,000 times each. I only need to run these functions on the rows returned after the pivot is completed. I believe this would save a TON of time. Any way of doing this on the rows AFTER the pivot?
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/13/2013 :  15:04:54  Show Profile  Reply with Quote
You may want to consider creating another temp table dynamically to hold the results from the pivot query, then update the new table with the output from your function calls.
As shown below: [make sure to modify and test the update query in italic below to get what you want...]
In additon you may want to consider optimizing your two functions.


CREATE TABLE #Temp
(
ServiceRecordID INT,
ServiceName VARCHAR(MAX),
LocationStd VARCHAR(1000),
AreaServedStd VARCHAR(1000),
RegionalLimited BIT,
Region VARCHAR(255),
Visible BIT,
OtherSites VARCHAR(1000)
)

INSERT INTO #Temp
SELECT TOP 100 SR.ServiceRecordID, '', SR.LocationStd, SR.AreaServedStd, SR.RegionalLimited, R.Region,
CASE WHEN (ISNULL(R_SR.RegionID,0) = 0 AND ISNULL(R_SR_Serv.RegionID,0) = 0) THEN 0 ELSE 1 END AS Visible, ''
FROM ServiceRecord SR
INNER JOIN Sites S ON SR.SiteID = S.SiteID
INNER JOIN Regions R ON R.SiteID = S.SiteID
LEFT OUTER JOIN lkup_Region_ServiceRecord R_SR ON R_SR.RegionID = R.RegionID AND R_SR.ServiceRecordID = SR.ServiceRecordID
LEFT OUTER JOIN lkup_Region_ServiceRecord_Serv R_SR_Serv ON R_SR_Serv.RegionID = R.RegionID AND R_SR_Serv.ServiceRecordID = SR.ServiceRecordID AND SR.RegionalLimited = 0
WHERE SR.SiteID = @SiteID
AND R.RegionID % 100 != 0


DECLARE @RegionList varchar(2000),@SQL varchar(max), @TBLECREATESQL VARCHAR(max), @RegionCList VARCHAR(2000);

SELECT @RegionList = STUFF((SELECT DISTINCT ',[' + Region + ']' FROM #Temp ORDER BY ',[' + Region + ']' FOR XML PATH('')),1,1,'')

SELECT @RegionCList = STUFF((SELECT DISTINCT ',[' + Region + '] INT' FROM #Temp ORDER BY ',[' + Region + '] INT' FOR XML PATH('')),1,1,'')

IF OBJECT_ID('dbo.Temp1') IS NOT NULL DROP TABLE dbo.Temp1;

SET @TBLECREATESQL = 'CREATE TABLE dbo.Temp1 (
ServiceRecordID INT,
LocationStd VARCHAR(1000),
AreaServedStd VARCHAR(1000),
RegionalLimited BIT,' + @RegionCList +
', ServiceName VARCHAR(MAX) NULL, OtherSites VARCHAR(1000) NULL);'

SET @SQL='SELECT *, 0, 0 FROM 
(SELECT ServiceRecordID,
LocationStd,
AreaServedStd,
RegionalLimited, 
Region As Region,
CAST(Visible AS INT) AS Visible FROM #Temp) B PIVOT(MAX(Visible) FOR Region IN (' + @RegionList + ')) A'


EXEC (@TBLECREATESQL);

INSERT INTO dbo.Temp1 EXEC(@SQL);

UPDATE T1 
	SET ServiceName = dbo.fn_ServiceRecordGetServiceName(SR.ServiceRecordID,''), 
	OtherSites = dbo.fn_GetOtherRegionalSitesForServiceRecord(SR.ServiceRecordID)
	FROM dbo.Temp1 T1, ServiceRecord SR
INNER JOIN Sites S ON SR.SiteID = S.SiteID
INNER JOIN Regions R ON R.SiteID = S.SiteID
LEFT OUTER JOIN lkup_Region_ServiceRecord R_SR ON R_SR.RegionID = R.RegionID AND R_SR.ServiceRecordID = SR.ServiceRecordID
LEFT OUTER JOIN lkup_Region_ServiceRecord_Serv R_SR_Serv ON R_SR_Serv.RegionID = R.RegionID AND R_SR_Serv.ServiceRecordID = SR.ServiceRecordID AND SR.RegionalLimited = 0
WHERE SR.SiteID = @SiteID
AND R.RegionID % 100 != 0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/14/2013 :  00:47:36  Show Profile  Reply with Quote
i doubt whether you need a function inthese case. it looks like seeing name of function the attempt is to get an associated values corresponding table column. If that being the case, you can very well get this using a simple join operation instead of using a UDF for that

scalar udf can cause performance issues for larger datasets as it will get invoked once per each row.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.11 seconds. Powered By: Snitz Forums 2000