August 11, 2009 at 12:45 pm
I have 3 tables
Table 1 is for storing cub scout names, IE SFname, Sname, SUniqueID
Table 2 is for storing parents names for each scout. IE PFname PLname, PUniqueID
Table 3 is for grouping multiple Parents to multiple Scouts. IE SUniqueID, PUniqueID
For example Table 1 (Scout) could have the following Information
SUniqueID | SFname | SLname
1 | Billy | Smith
2 | Ben | Smith
3 | John | Brady
NOTE: Both Billy and Ben have the same parents
Table 2 (Parent)
PUniqueID | PFname | PLname
1 | Dave | Smith
2 | Julie | Smith
3 | Frank | Brady
NOTE: both Dave and Julie are parents of Billy and Ben
Table 3 (Cross Reference Between both Scouts and Parents)
SUniqueID | PUniqueID
1 | 1
1 | 2
3 | 3
What would be the SQL for returning the records as follows
Expected Output
Scout Name | Parents Name
Billy Smith | Dave Smith, Julie Smith
Ben Smith | Dave Smith, Julie Smith
John Brady | Frank Brady
August 11, 2009 at 1:17 pm
You're going to need to use a PIVOT (or equivalent). If you want a more detailed answer, I suggest that you follow the forum etiquette guidelines and post the table definitions and the insert commands as suggested in at How to post data/code on a forum to get the best help[/url]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 11, 2009 at 5:21 pm
Sorry for the bad etiquette. I have included all the necessary SQL or creating tables and for inserting some data. Again the output should like the following:
Column 1 (Scouts Name), Column 2 (Parents/s)
If more than one parent is found both parents should be concatinated into one record. So for our example it should like this:
John Smith, Bob Smith and Julie Smith
Billy Smith, Bob Smith and Julie Smith
Eric Brady, Dave Brady
Here is the SQL
USE [Northwind]
GO
-- TABLE 1 for listing scout information
CREATE TABLE [dbo].[SCOUTS] (
[SCT_ID] int IDENTITY(1,1) NOT NULL,
[SCT_FNAME] nvarchar(25) NOT NULL,
[SCT_LNAME] nvarchar(25) NOT NULL
CONSTRAINT [PK_SCOUTS] PRIMARY KEY([SCT_ID])
)
GO
-- TABLE 2 for listing parent/guardian information
CREATE TABLE [dbo].[PARENT_GUARDIAN] (
[PG_ID] int IDENTITY(1,1) NOT NULL,
[PG_FNAME] nvarchar(25) NOT NULL,
[PG_LNAME] nvarchar(25) NOT NULL
CONSTRAINT [PK_PARENT_GUARDIAN] PRIMARY KEY([PG_ID])
)
GO
-- 3rd table for referencing multiple scouts to multiple parents many to many on both sides
CREATE TABLE [dbo].[xref_PG_SCT] (
[PG_ID] int NOT NULL,
[SCT_ID]int NOT NULL
)
GO
ALTER TABLE [dbo].[xref_PG_SCT]
ADD CONSTRAINT [FK_xref_PG_SCT_SCOUTS]
FOREIGN KEY([SCT_ID])
REFERENCES [dbo].[SCOUTS]([SCT_ID])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[xref_PG_SCT]
ADD CONSTRAINT [FK_xref_PG_SCT_PARENT_GUARDIAN]
FOREIGN KEY([PG_ID])
REFERENCES [dbo].[PARENT_GUARDIAN]([PG_ID])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
GO
-- lets insert some data
-- John and Billy Smith are bothers, Eric is not related
INSERT INTO [dbo].[SCOUTS]([SCT_FNAME], [SCT_LNAME])
VALUES(N'John', N'Smith')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_FNAME], [SCT_LNAME])
VALUES(N'Billy', N'Smith')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_FNAME], [SCT_LNAME])
VALUES(N'Eric', N'Brady')
GO
-- Bob and Julie are parents to both Billy and John, Dave is
-- father to Eric
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_FNAME], [PG_LNAME])
VALUES( N'Bob', N'Smith')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_FNAME], [PG_LNAME])
VALUES( N'Julie', N'Smith')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_FNAME], [PG_LNAME])
VALUES( N'Dave', N'Brady')
GO
-- insert cross reference data ID's between SCOUTS and PARENT_GUARDIAN
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(1,1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(1,2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(2,1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(2,2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(3,3)
GO
August 11, 2009 at 9:24 pm
That's much better. Try the following code:
SELECT SCT_NAME, [1] + IsNull(', ' + [2], '') AS Parents
FROM (
SELECT
Scouts.SCT_ID
, Scouts.SCT_FNAME + ' ' + SCT_LNAME AS SCT_NAME
, Row_Number() OVER( PARTITION BY Scouts.SCT_ID ORDER BY Scouts.SCT_ID, Parent_Guardian.PG_ID) AS RowNum
, Parent_Guardian.PG_FNAME + ' ' + Parent_Guardian.PG_LNAME AS PG_Name
FROM Scouts
INNER JOIN xref_PG_SCT
ON Scouts.SCT_ID = xref_PG_SCT.SCT_ID
INNER JOIN Parent_Guardian
ON xref_PG_SCT.PG_ID = Parent_Guardian.PG_ID
) p
PIVOT (
Max(PG_Name)
FOR RowNum IN ([1], [2])
) AS Pvt
ORDER BY SCT_ID
You need to pivot on the row number rather than the parent ID in order to limit the number of pivot columns to two.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2009 at 8:18 am
I don't have much experience using PIVOT, but I think Drew's solution will only work if each scout only has two parents. In the case of stepparents, this may not always be true. If you need the solution to work for those scenarios as well, you may have to use a cursor and a temp table. There might be a non-cursor solution -- some people on here have claimed that there's always one, but here's a cursor solution just in case that's what you need.
DECLARE @JoinedTable TABLE
(
ScoutName NVARCHAR( 75 )
, ParentsName NVARCHAR( 300 )
)
DECLARE @TempId INT
DECLARE @TempParentsNames NVARCHAR( 300 )
DECLARE TheCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT SCT_ID
FROM SCOUTS
OPEN TheCursor
FETCH FROM TheCursor INTO
@TempId
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @TempParentsNames = ''
SELECT @TempParentsNames = @TempParentsNames + Parents.PG_FNAME + ' ' + Parents.PG_LNAME + ', '
FROM SCOUTS Scout
LEFT JOIN xref_PG_SCT xref
ON xref.SCT_ID = Scout.SCT_ID
LEFT JOIN PARENT_GUARDIAN Parents
ON xref.PG_ID = Parents.PG_ID
WHERE Scout.SCT_ID = @TempId
SET @TempParentsNames = LEFT( RTRIM( @TempParentsNames ), LEN( RTRIM( @TempParentsNames ) ) - 1 )
INSERT INTO @JoinedTable
(
ScoutName
, ParentsName
)
SELECT Scout.SCT_FNAME + ' ' + Scout.SCT_LNAME AS [Scout Name]
, @TempParentsNames AS [ParentName]
FROM SCOUTS Scout
WHERE Scout.SCT_ID = @TempId
FETCH FROM TheCursor INTO
@TempId
END
CLOSE TheCursor
DEALLOCATE TheCursor
SELECT *
FROM @JoinedTable
August 12, 2009 at 9:11 am
Julie Zeien (8/12/2009)
I don't have much experience using PIVOT, but I think Drew's solution will only work if each scout only has two parents.
My solution will work for stepparents as well, you would just need to add more columns to the pivot. I don't think that you would ever need more than four columns: one for each of the biological parents, and one for each of their current spouses.
The cursor solution you proposed is horribly inefficient, because you have to access the Scout table N+1 times where N is the number of scouts in the table. If you are going to propose a RBAR solution, you could at least try to make it "efficient", by defining your cursor like this:
DECLARE TheCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT Scouts.SCT_ID
, Scouts.SCT_FNAME + ' ' + SCT_LNAME AS SCT_NAME
, Parent_Guardian.PG_ID
, Parent_Guardian.PG_FNAME + ' ' + Parent_Guardian.PG_LNAME AS PG_Name
FROM SCOUTS Scout
LEFT JOIN xref_PG_SCT xref
ON xref.SCT_ID = Scout.SCT_ID
LEFT JOIN PARENT_GUARDIAN Parents
ON xref.PG_ID = Parents.PG_ID
That at least doesn't compound the inefficiency of the cursor by accessing the table multiple times.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2009 at 1:31 pm
drew.allen (8/12/2009)
My solution will work for stepparents as well, you would just need to add more columns to the pivot. I don't think that you would ever need more than four columns: one for each of the biological parents, and one for each of their current spouses.
This is most likely true for this particular problem. I can't see any need to have more than 4 columns either, but let's say for a minute that we don't know how many columns we will need. Maybe the OP will eventually need a different sort of report that lists each parent individually and has all their kids in the second column. If that in fact happens, we won't know how many columns to add to the pivot. Are you suggesting that they should just add columns as needed each time the parents have more children who are in scouts?
I am not claiming that the cursor is efficient. However, it is more easily scalable. Also, since you have brought up the topic of efficiency, I'd like to see how you would write the remainder of the code for your more efficient cursor. The reason I am reading N+1 times is because I only want to insert 1 row for each scout in the temp table. Your cursor will loop more than N times as well because it will have more than 1 row for each scout, and besides that, I don't think you can do what the OP needs to be done with all the data in the cursor like that.
Correct me if I'm wrong. I'm not saying the OP shouldn't use the PIVOT solution. I'm just saying that if they wanted something that didn't care how many names might be in the second column that they should use a cursor. Thanks for your input.
August 12, 2009 at 2:14 pm
Julie Zeien (8/12/2009)
Correct me if I'm wrong. I'm not saying the OP shouldn't use the PIVOT solution. I'm just saying that if they wanted something that didn't care how many names might be in the second column that they should use a cursor. Thanks for your input.
A cursor is almost never the correct approach. Here is a simpler solution that does not use a cursor and works for any number of columns returned.
SELECT
s.Sct_ID
, s.Sct_FName + ' ' + s.Sct_LName AS Sct_Name
, Stuff( (
SELECT ', ' + pg.PG_FName + ' ' + pg.PG_LName
FROM Parent_Guardian AS pg
INNER JOIN xref_PG_SCT AS xref
ON pg.PG_ID= xref.PG_ID
WHERE xref.Sct_ID = s.Sct_ID
ORDER BY pg.PG_ID
FOR XML Path('') )
, 1, 2, '') AS PG_Name
FROM Scouts AS s
The Stuff() function simply strips off the initial ', '
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2009 at 2:21 pm
Drew, that last query is beautiful. Kudos to you!
August 15, 2009 at 3:48 am
Drew and Julie thank you for so many great examples.
Drew I did run your final post code and received the following error:
>[Error] Script lines: 1-14 -------------------------
Incorrect syntax near the keyword 'FOR'.
Msg: 156, Level: 15, State: 1, Procedure: , Line: 11
I think its because FOR XML PATH is a 2005 thing I only have 2000
Here is your code :
SELECT
S.SCT_ID
, S.SCT_FNAME + ' ' + S.SCT_LNAME AS SCT_NAME
, STUFF( (
SELECT ', ' + PG.PG_FNAME + ' ' + PG.PG_LNAME
FROM PARENT_GUARDIAN AS PG
INNER JOIN XREF_PG_SCT AS XREF
ON PG.PG_ID= XREF.PG_ID
WHERE XREF.SCT_ID = S.SCT_ID
ORDER BY PG.PG_ID
FOR XML PATH('') )
, 1, 2, '') AS PG_NAME
FROM SCOUTS AS S
Thanks again I appreciate both your efforts, and he definitely pays to post with etiquette
August 15, 2009 at 12:12 pm
Having read that you are using SQL Server 2000, not SQL Server 2005 you really should also pay a little closer attention to where you post your request for help. Having posted your request in a SQL Server 2005 forum, you got a SQL Server 2005 answer.
August 15, 2009 at 1:44 pm
netguykb (8/15/2009)
I think its because FOR XML PATH is a 2005 thing I only have 2000[...]
Thanks again I appreciate both your efforts, and he definitely pays to post with etiquette
Well, part of the etiquette includes posting questions in the correct forum. If you need a SQL 2000 solution, then don't post it in the SQL 2005 forum.
You can still use a manual version of the PIVOT solution (PIVOT also being 2005). I no longer have access to a SQL 2000 machine, so I can't test it on 2000, but try the following.
SELECT Sct_FName, PG_Name_1 + CASE WHEN PG_Name_1 PG_Name_2 THEN ', ' + PG_Name_2 ELSE '' END AS PG
FROM Scouts
INNER JOIN (
SELECT
xref_PG_SCT.SCT_ID AS Scout_ID
, Min(Parent_Guardian.PG_FNAME + ' ' + Parent_Guardian.PG_LNAME) AS PG_Name_1
, Max(Parent_Guardian.PG_FNAME + ' ' + Parent_Guardian.PG_LNAME) AS PG_Name_2
FROM xref_PG_SCT
INNER JOIN Parent_Guardian
ON xref_PG_SCT.PG_ID = Parent_Guardian.PG_ID
GROUP BY xref_PG_SCT.SCT_ID
) AS PG
ON Scouts.SCT_ID = PG.Scout_ID
This has several disadvantages to the SQL 2005 version. First, it's sorted by the PG First Name rather than the PG ID, which means that you can't control the order. Second, the Min() and Max() version only returns two values. If you want to include step-parents, you're going to have to use another approach.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply