November 21, 2012 at 6:14 am
Hi,
I've got a problem that may or may not have a possible solution. What I'm essentially trying to achieve is for two rows to be returned in the same row - no merging of data or anything like that, just two rows as they are returned as one row. The data (simplified here) I'm using that I need to achieve with this is as follows...
TABLE: SITE
PK_SITE_ID | FK_ID | PLAN_DESC | ACTUAL_DESC |
1 101 Some plan text. Some actual text.
2 102 Some other plan text. Some other actual text.
Ok so then the next step I want to do that I'm having trouble with is create a stored procedure/view/function etc... to return the following....
FK_SITE_ID_1 | FK_ID_1 | PLAN_DESC_1 | ACTUAL_DESC_1 | FK_SITE_ID_2 | FK_ID_2 | PLAN_DESC_2 | ACTUAL_DESC_2
1 101 Some plan text. Some actual text. 2 102 Some other plan text. Some actual text.
The reason for doing this is that this data is used on a report (Crystal Reports) and each report needs to contain the data for two sites. If I can get two sites in one row then I can set Crystal to create a new report every row. The report is pretty complex and isn't just a case of one sites data being in the top half of the report and the second sites being in the bottom half because then I could have just had it duplicate that part of the report per row. Also there the number of sites is not pre-determined.
I have looked all over for a possible solution for this with no luck so hopefully someone here will be able to point me in the right direction of how to solve this or just let me know if it isn't even possible.
Cheers
November 21, 2012 at 7:29 am
This is is no way elegant or the best solution to the problem and I have made some assumptions about the table field types and lengths etc and will be limited to the number of columns a table can hold but this works on my machine. Give it a blast
-- Site Table Creation so we can work with it
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Sites]')
AND type IN ( N'U' ) )
DROP TABLE [dbo].[Sites]
SET NOCOUNT ON
CREATE TABLE Sites
(
PK_Site_ID INT ,
FK_ID INT ,
Plan_Desc NVARCHAR(100) ,
Actual_Desc NVARCHAR(100)
)
-- Insert the test values into the sites table
INSERT INTO sites
VALUES ( 1, -- PK_Site_ID - int
101, -- FK_ID - int
N'Some Plan Text', -- Plan_Desc - nvarchar(100)
N'Some Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO sites
VALUES ( 2, -- PK_Site_ID - int
102, -- FK_ID - int
N'Some More Plan Text', -- Plan_Desc - nvarchar(100)
N'Some More Actual Text' -- Actual_Desc - nvarchar(100)
)
-- Get a list of columns for each site in the site table for processing so we can build up a SQL string
DECLARE @ToProcess TABLE
(
ProcessingKey INT ,
PK_SiteID INT ,
ColumnName NVARCHAR(100)
)
INSERT INTO @ToProcess
( ProcessingKey ,
PK_SiteID ,
ColumnName
)
SELECT ROW_NUMBER() OVER ( ORDER BY PK_Site_ID, ORDINAL_POSITION ) ,
PK_Site_ID ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
CROSS JOIN dbo.Sites
WHERE TABLE_NAME = 'Sites'
ORDER BY PK_Site_ID ,
ORDINAL_POSITION
DECLARE @sql NVARCHAR(MAX)
DECLARE @SiteID INT
DECLARE @ColumnName NVARCHAR(100)
-- Go away and create the table below for all sites in the sites table
SET @sql = 'CREATE TABLE tmp_TransposedInformation ('
WHILE ( SELECT COUNT(*)
FROM @ToProcess
) > 0
BEGIN
SET @SiteID = ( SELECT TOP 1
PK_SiteID
FROM @ToProcess
ORDER BY ProcessingKey ASC
)
SET @ColumnName = ( SELECT TOP 1
ColumnName
FROM @ToProcess
ORDER BY ProcessingKey ASC
)
SET @sql = @sql + @columnname + CAST(@SiteID AS CHAR(2))
+ ' nvarchar(100),'
DELETE FROM @ToProcess
WHERE ProcessingKey = ( SELECT TOP 1
ProcessingKey
FROM @ToProcess
ORDER BY ProcessingKey ASC
)
END
SET @sql = LEFT(@sql, LEN(@sql) - 1) + ')'
-- If it already exists drop it prior to re-creating
IF OBJECT_ID('tmp_TransposedInformation') IS NOT NULL
DROP TABLE tmp_TransposedInformation
EXECUTE sp_executesql @sql
-- Create a series of update statements to update the relevant columns depending on the site code number it is in the list
INSERT INTO @ToProcess
( ProcessingKey ,
PK_SiteID ,
ColumnName
)
SELECT ROW_NUMBER() OVER ( ORDER BY PK_Site_ID ) ,
PK_Site_ID ,
NULL
FROM dbo.Sites
ORDER BY PK_Site_ID
INSERT INTO tmp_TransposedInformation
( PK_Site_ID1 )
SELECT '0'
DECLARE @SiteChar CHAR(2)
WHILE ( SELECT COUNT(*)
FROM @ToProcess
) > 0
BEGIN
SET @SiteChar = CAST(( SELECT TOP 1
PK_SiteID
FROM @ToProcess
ORDER BY ProcessingKey ASC
) AS CHAR(2))
SET @sql = 'UPDATE dbo.tmp_TransposedInformation SET '
SET @Sql = @sql + 'PK_Site_ID' + @SiteChar + ' = s.PK_Site_ID, FK_ID'
+ @SiteChar + ' = s.FK_ID, Plan_Desc' + @SiteChar
+ ' = s.Plan_Desc, Actual_Desc' + @SiteChar + ' = s.Actual_Desc'
SET @sql = @sql + ' FROM dbo.Sites s WHERE PK_Site_ID = ' + @SiteChar
EXECUTE sp_executesql @sql
DELETE FROM @ToProcess
WHERE PK_SiteID = CAST(@SiteChar AS INT)
END
SELECT *
FROM dbo.tmp_TransposedInformation
November 21, 2012 at 3:01 pm
Thank you for that, I appreciate the effort! I will have a look at it and see if I can make it work for my full solution.
Cheers
November 21, 2012 at 7:06 pm
Call me weird or whatever but I don't think this is particularly messy at all using a simple cross tab query.
First, Michael's set up data with a couple of additional records for illustration.
-- Site Table Creation so we can work with it
CREATE TABLE #Sites
(
PK_Site_ID INT ,
FK_ID INT ,
Plan_Desc NVARCHAR(100) ,
Actual_Desc NVARCHAR(100)
)
-- Insert the test values into the sites table
INSERT INTO #Sites
VALUES ( 1, -- PK_Site_ID - int
101, -- FK_ID - int
N'Some Plan Text', -- Plan_Desc - nvarchar(100)
N'Some Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO #Sites
VALUES ( 2, -- PK_Site_ID - int
102, -- FK_ID - int
N'Some More Plan Text', -- Plan_Desc - nvarchar(100)
N'Some More Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO #Sites
VALUES ( 3, -- PK_Site_ID - int
103, -- FK_ID - int
N'Some Plan Text', -- Plan_Desc - nvarchar(100)
N'Some Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO #Sites
VALUES ( 4, -- PK_Site_ID - int
104, -- FK_ID - int
N'Some More Plan Text', -- Plan_Desc - nvarchar(100)
N'Some More Actual Text' -- Actual_Desc - nvarchar(100)
)
Next my solution:
;WITH Sites AS (
SELECT PK_Site_ID, FK_ID, Plan_Desc, Actual_Desc
,n=ROW_NUMBER() OVER (ORDER BY PK_Site_ID)%2
,m=(ROW_NUMBER() OVER (ORDER BY PK_Site_ID)-1)/2
FROM #Sites)
SELECT FK_SITE_ID_1=MAX(CASE n WHEN 0 THEN PK_Site_ID END)
,FK_ID_1=MAX(CASE n WHEN 0 THEN FK_ID END)
,PLAN_DESC_1=MAX(CASE n WHEN 0 THEN PLAN_DESC END)
,ACTUAL_DESC_1=MAX(CASE n WHEN 0 THEN ACTUAL_DESC END)
,FK_SITE_ID_2=MAX(CASE n WHEN 1 THEN PK_Site_ID END)
,FK_ID_2=MAX(CASE n WHEN 1 THEN FK_ID END)
,PLAN_DESC_2=MAX(CASE n WHEN 1 THEN PLAN_DESC END)
,ACTUAL_DESC_2=MAX(CASE n WHEN 1 THEN ACTUAL_DESC END)
FROM Sites
GROUP BY m
DROP TABLE #Sites
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 21, 2012 at 8:19 pm
dwain.c (11/21/2012)
Call me weird or whatever but I don't think this is particularly messy at all using a simple cross tab query.First, Michael's set up data with a couple of additional records for illustration.
-- Site Table Creation so we can work with it
CREATE TABLE #Sites
(
PK_Site_ID INT ,
FK_ID INT ,
Plan_Desc NVARCHAR(100) ,
Actual_Desc NVARCHAR(100)
)
-- Insert the test values into the sites table
INSERT INTO #Sites
VALUES ( 1, -- PK_Site_ID - int
101, -- FK_ID - int
N'Some Plan Text', -- Plan_Desc - nvarchar(100)
N'Some Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO #Sites
VALUES ( 2, -- PK_Site_ID - int
102, -- FK_ID - int
N'Some More Plan Text', -- Plan_Desc - nvarchar(100)
N'Some More Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO #Sites
VALUES ( 3, -- PK_Site_ID - int
103, -- FK_ID - int
N'Some Plan Text', -- Plan_Desc - nvarchar(100)
N'Some Actual Text' -- Actual_Desc - nvarchar(100)
)
INSERT INTO #Sites
VALUES ( 4, -- PK_Site_ID - int
104, -- FK_ID - int
N'Some More Plan Text', -- Plan_Desc - nvarchar(100)
N'Some More Actual Text' -- Actual_Desc - nvarchar(100)
)
Next my solution:
;WITH Sites AS (
SELECT PK_Site_ID, FK_ID, Plan_Desc, Actual_Desc
,n=ROW_NUMBER() OVER (ORDER BY PK_Site_ID)%2
,m=(ROW_NUMBER() OVER (ORDER BY PK_Site_ID)-1)/2
FROM #Sites)
SELECT FK_SITE_ID_1=MAX(CASE n WHEN 0 THEN PK_Site_ID END)
,FK_ID_1=MAX(CASE n WHEN 0 THEN FK_ID END)
,PLAN_DESC_1=MAX(CASE n WHEN 0 THEN PLAN_DESC END)
,ACTUAL_DESC_1=MAX(CASE n WHEN 0 THEN ACTUAL_DESC END)
,FK_SITE_ID_2=MAX(CASE n WHEN 1 THEN PK_Site_ID END)
,FK_ID_2=MAX(CASE n WHEN 1 THEN FK_ID END)
,PLAN_DESC_2=MAX(CASE n WHEN 1 THEN PLAN_DESC END)
,ACTUAL_DESC_2=MAX(CASE n WHEN 1 THEN ACTUAL_DESC END)
FROM Sites
GROUP BY m
DROP TABLE #Sites
And, look Ma! No WHILE loop!
Nice Crosstab and Modulus, Dwain.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2012 at 8:22 pm
Jeff Moden (11/21/2012)
And, look Ma! No WHILE loop!Nice Crosstab and Modulus, Dwain.
Aw shucks, Master. T'weren't nuthin'!:blush:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 22, 2012 at 1:58 am
Ah yes. I misread the original spec. Your method way better
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply