Recursive Self Join
-
I want to be able to take a table and run a query such that the contents of the column become the column names in the join.
For example from the following:
CREATE TABLE #T (x varchar(1), # varchar(1), n int);
INSERT INTO #T (x,#,n) VALUES ('a', '1', 1);
INSERT INTO #T (x,#,n) VALUES ('a', '2', 2);
INSERT INTO #T (x,#,n) VALUES ('a', '3', 3);
INSERT INTO #T (x,#,n) VALUES ('b', '1', 4);
INSERT INTO #T (x,#,n) VALUES ('b', '2', 5);
INSERT INTO #T (x,#,n) VALUES ('b', '3', 6);
INSERT INTO #T (x,#,n) VALUES ('c', '1', 7);
INSERT INTO #T (x,#,n) VALUES ('c', '2', 8);
INSERT INTO #T (x,#,n) VALUES ('c', '3', 9);
My desired result is
# a b c
1 1 4 7
2 2 5 8
3 3 6 9
-
The following code produces
x 1 2 3
a 1 2 3
b 4 5 6
c 7 8 9
/*
from thread found here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=242919#bm243627
*/
declare @debug int
set @debug = 0 --Set to 1 if you want this to print
Set NOCount On
---===== If the temp table to hold the results in exists, drop it
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
---===== Populate the temp table with results from your original query
SELECT x,
#,
n
INTO #Results
from #t
IF @@RowCount = 0
Return
--===== Declare some local variables to hold some Dynamic SQL
DECLARE
@MySQL1 VARCHAR(8000)
DECLARE
@MySQL2 VARCHAR(8000)
DECLARE
@MySQL3 VARCHAR(8000)
--===== Build the SELECT clause
SET @MySQL1 = 'SELECT x,'
--===== Build the select LIST (do not try to reformat or you'll mess it up!)
SELECT @MySQL2 = ISNULL(@MySQL2 + ',','') + '
MIN(CASE WHEN # = ''' + # + '''
THEN n ELSE NULL END) AS ' + '[' + # + ']'
FROM (SELECT DISTINCT TOP 100 PERCENT # AS #
FROM #Results
Order by #) d
--===== Build the FROM and GROUP BY clauses
SELECT @MySQL3 = CHAR(13) + 'FROM #Results GROUP BY x'
--===== Display the resulting SQL (you can take this piece out, just for demo)
if @Debug = 1
PRINT @MySQL1+@MySQL2+@MySQL3
ELSE
--===== Execute the Dynamic SQL
EXEC ( @MySQL1 +@MySQL2 +@MySQL3 )
-
thanks much!
I will play with this as there are several "tables" in my application that require this.
-
Outstanding... you were correct from your other post in that...
1. You would help others
2. You could find it again.
I remember...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good isn't it. Still owe you a beer Jeff
Dave
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply