May 14, 2010 at 1:45 pm
Hopefully I can explain this. I've been tasked to extend an export procedure. Currently, the export procedure is generating all the data in a stored procedure using one large select statement, and it works.
The request is to add 6 data columns to the select, which consist of the 1st, second, third, 4th, 5th and 6th values from another table which has a relationship with my select clause.
I've tried a few options, and the closest so far is to
SELECT top 1 name from (select top 1 personsequence, name from tblA WHERE ID = mainTable.ID ORDER BY personsequence ASC) sub order by personsequence desc,
SELECT top 1 name from (select top 2 personsequence, name from tblA WHERE ID = mainTable.ID ORDER BY personsequence ASC) sub order by personsequence desc,
SELECT top 1 name from (select top 3 personsequence, name from tblA WHERE ID = mainTable.ID ORDER BY personsequence ASC) sub order by personsequence desc,
The problem with that chunk, is if there are less than 3 names, then the last name is repeated for the last queries. My thought to get this solution to work i to add to each query a condition that the count(*) is >= N or whatever, but I've been fighting this for a while.
I tried a few other things, such as wrapping the "select nth" into a stored procedure, which worked great as a stored procedure but then i was having trouble adding stored procedure as a column in my select statement.
I then tried sticking it into a UDF, but for me to specify the Nth as a parameter to the top required dynamic SQL which caused the need for exec which caused....
So two questions come out of this:
1) Can someone suggest a way to modify the above select's to add a condition that the count(*) is >= N
2) Can someone recommend how I might use OUTPUT or PRINT as part of a stored procedure so that I can call that stored procedure as a column in my big select statement?
May 14, 2010 at 1:55 pm
Quick update, what I did for now was to create 6 functions that basically:
Here's my getperson6 function for example:
select @count = count(*) from tblA where ID = @ID
if @count >= 6
SELECT top 1 @name = name from (select top 6 personsequence, name from tblA WHERE ID = @ID ORDER BY personsequence ASC) sub order by personsequence desc
return @actor
Surely there's a BETTER WAY!
May 14, 2010 at 2:02 pm
Check out ROW_NUMBER().
And read my article[/url] on how to use the row_number ranking function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 14, 2010 at 2:04 pm
I would suggest ROW_NUMBER() OVER() then select RowNumber=1, RowNumber=2, etc.
Check here for details ... http://msdn.microsoft.com/en-us/library/ms186734.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 14, 2010 at 3:29 pm
Here's two methods to accomplish this:
-- See how you start off by actually creating a table
-- and then inserting the data into it? Doing this really
-- makes things a lot easier for all the people you want to help
-- you. So, HELP US HELP YOU by doing this for us!
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @TblA TABLE (
ID int,
PersonSequence int,
Name varchar(50)
)
INSERT INTO @TblA
SELECT 1,1,'1-1' UNION ALL
SELECT 1,2,'1-2' UNION ALL
SELECT 1,3,'1-3' UNION ALL
-- notice how up to now the numbers are sequential
-- and how everything from here on skips numbers...
SELECT 1,5,'1-5' UNION ALL
SELECT 1,7,'1-7' UNION ALL
SELECT 1,9,'1-9' UNION ALL
-- ensure that there's an extra row you don't want returned
SELECT 1,19,'1-19' UNION ALL
-- and finally do the next ID, only 3 PersonSequence/Name values
SELECT 2,2,'2-2' UNION ALL
SELECT 2,4,'2-4' UNION ALL
SELECT 2,6,'2-6'
-- driving table
DECLARE @TblB TABLE (ID int)
INSERT INTO @TblB
SELECT 1 UNION ALL
SELECT 2
-- whew!!! now that the test data has been made,
-- let's see how to do what you're asking for!
-- METHOD 1 - Manually Pivot the data
SELECT b.ID,
[1] = MAX(CASE WHEN a.RowNbr = 1 THEN a.Name ELSE NULL END),
[2] = MAX(CASE WHEN a.RowNbr = 2 THEN a.Name ELSE NULL END),
[3] = MAX(CASE WHEN a.RowNbr = 3 THEN a.Name ELSE NULL END),
[4] = MAX(CASE WHEN a.RowNbr = 4 THEN a.Name ELSE NULL END),
[5] = MAX(CASE WHEN a.RowNbr = 5 THEN a.Name ELSE NULL END),
[6] = MAX(CASE WHEN a.RowNbr = 6 THEN a.Name ELSE NULL END)
FROM (SELECT ID,
RowNbr = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PersonSequence),
Name
FROM @TblA) a
JOIN @TblB b
ON a.ID = b.ID
GROUP BY b.ID
ORDER BY b.ID
-- METHOD 2: Utilize the PIVOT operator
SELECT b.ID,
a.[1],
a.[2],
a.[3],
a.[4],
a.[5],
a.[6]
FROM (SELECT ID,
RowNbr = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY PersonSequence),
Name
FROM @TblA) UselessButRequiredAlias
PIVOT (MAX(Name) FOR RowNbr in ([1],[2],[3],[4],[5],[6])) a
JOIN @TblB b
ON b.ID = a.ID
ORDER BY b.ID
These two methods handle your issue, but didn't answer the questions that you did ask. Let us know if you still need them answered.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 15, 2010 at 3:55 am
Very nice, Wayne 😎
I just know you'd like to see an APPLY in there somewhere, so:
SELECT B.ID,
iTVF.*
FROM @TblB B
CROSS
APPLY (
SELECT Pivoted.*
FROM (
SELECT TOP (6)
A.Name,
ROW_NUMBER() OVER (ORDER BY A.PersonSequence)
FROM @TblA A
WHERE A.ID = B.ID
ORDER BY A.PersonSequence
) SubQuery (name, rn)
PIVOT (
MAX(name) FOR rn IN ([1],[2],[3],[4],[5],[6])
) Pivoted
) iTVF;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 15, 2010 at 6:57 am
LOL! I should have known Paul would show a CROSS APPLY.
I HAVE to start thinking... can this be done with a CROSS APPLY? I just don't consider it.
I also like the TOP 6. Nice:cool:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 15, 2010 at 7:50 am
WayneS (5/15/2010)
LOL! I should have known Paul would show a CROSS APPLY.
😀
I HAVE to start thinking... can this be done with a CROSS APPLY? I just don't consider it.
Think: Could I write a function to return the top 6 records for an ID? Of course!
I also like the TOP 6. Nice:cool:
It's the thing that might make the APPLY more efficient, if there were a large number of records for an ID.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply