Help returning data columns as a row

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 2012 Forums

Transact-SQL (2012)

Help returning data columns as a row

Author

Topic

donkyho
Starting Member

12 Posts

Posted - 2013-06-12 : 10:03:29

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-12 : 10:08:50

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 10:11:07

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

donkyho
Starting Member

12 Posts

Posted - 2013-06-12 : 11:01:28

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)

donkyho
Starting Member

12 Posts

Posted - 2013-06-12 : 11:28:02

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 11:35:01

cool

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-12 : 11:51:17

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)

donkyho
Starting Member

12 Posts

Posted - 2013-06-13 : 10:21:23

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)

donkyho
Starting Member

12 Posts

Posted - 2013-06-13 : 10:36:23

I think I figured it out.

adding 'ORDER BY SR.ServiceRecordID' to the main SELECT fixed it

donkyho
Starting Member

12 Posts

Posted - 2013-06-13 : 12:17:42

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?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-13 : 15:04:54

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.
[CODE]

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
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 00:47:36

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources