Self-Join one table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Self-Join one table

Author

Topic

master_axe
Starting Member

3 Posts

Posted - 2010-09-03 : 00:29:54

Hey,

my question is pretty straight forward. I want to do a self-join so as to use every ID only once.
I would then use COALESCE to summarize the values in one column, as every PropertyID only has 1 value. A simple JOIN on ID produces one row for every combination, and since I have 4 Datatypes, and up to 4 different Properties per ID, that can get messy. Should I simply add "v1.propertyID is not v2.propertyID"? Would i use 'is not' or '!='? Thank you forum, kind regards

Franz

P.S.: Also, one of the datatypes is 'varbinary', so I can't put it in the 'COALESCE'. Is the a way around that, such as CAST? Thanks again ^^


CREATE TABLE Table101(
	[PrimitiveID] NOT NULL,
	[PrimitivePropertyID] [int] NOT NULL,
	[PrimitivePropertyIndex] [smallint] NOT NULL,
	[IntegerValue] [bigint] SPARSE  NULL,
	[NumericValue] [float] SPARSE  NULL,
	[StringValue] [nvarchar](255) SPARSE  NULL,
	[BinaryValue] [varbinary](2048) SPARSE  NULL,
	[EnumValue] [int] SPARSE  NULL
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-03 : 11:43:20

What you do want as output?

Here is a link that might help all of us. It'll show you how to provide DDL, DML and expected output in a consumable format so that we can help you better.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

master_axe
Starting Member

3 Posts

Posted - 2010-09-03 : 13:45:08

hmmm, given *creating* a table isnt in the scope of my knowledge, this could prove tedious. Especially because the question is simple, and I fear the answer is really straight forward (me = big noob).


Name      Test#     Score_Type1      Score_Type2      Score_Type3  Score_Type4

Michael      1        75              NULL              NULL           NULL
Michael      2        NULL            57                NULL           NULL
Michael      3        NULL            NULL              95             NULL
Rachel       1        87              NULL              NULL           NULL
Rachel       3        NULL            NULL              87             NULL
David        4        NULL            NULL              NULL            67

I want to transform into


Name        Test#   Test Score    Test#    Test Score      Test#      Test Score
Michael       1       75            2        57              3            95
Rachel        1       87            3        90             NULL          NULL
David         4       67           NULL      NULL           NULL          NULL

Really, I want to query it, so that I get this result. I do not have privileges to manipulate or create tables. Is my request possible? Also, because I'd need COALESCE to to get from the 4 different Types to just the "Test Score", I cant specifically, because one of the "Types" is "varbinary" and wont fit in COALESCE. Thoughts?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-03 : 13:56:23

i don't understand your problem. your are posting DDL of some table which you are not using?

which SQL Server version are you using? in order to transpose your results?

master_axe
Starting Member

3 Posts

Posted - 2010-09-03 : 14:06:04

took me awhile. Maybe the edited post makes more sense now =). thank you for your patience.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-03 : 14:36:17

There are a bunch of ways to do this. Here is a quick method I hacked out:

DECLARE @T TABLE (Name VARCHAR(50), TestNum INT, Score_Type1 INT ,Score_Type2 INT, Score_Type3 INT,  Score_Type4 INT)
INSERT @T
SELECT 'Michael', 1, 75,   NULL, NULL, NULL
UNION ALL SELECT 'Michael', 2, NULL, 57,   NULL, NULL
UNION ALL SELECT 'Michael', 3, NULL, NULL, 95,   NULL
UNION ALL SELECT 'Rachel',  1, 87,   NULL, NULL, NULL
UNION ALL SELECT 'Rachel',  3, NULL, NULL, 87,   NULL
UNION ALL SELECT 'David',   4, NULL, NULL, NULL,  67


;WITH cteTest (Name, TestNum, Score, RowNum)
AS
(
	SELECT 
		Name, 
		TestNum, 
		(
			SELECT
				MAX(S.Score)
			FROM
			(
				SELECT T.Score_Type1 AS Score
				UNION ALL SELECT T.Score_Type2
				UNION ALL SELECT T.Score_Type3
				UNION ALL SELECT T.Score_Type4
			) AS S
		) AS MaxScore,
		ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TestNum) AS RowNum
	FROM
		@T AS T
)
SELECT 
	A.Name,
	A.TestNum,
	A.Score, 
	B.TestNum AS TestNum2, 
	B.Score AS Score2,
	B.TestNum AS TestNum2, 
	B.Score AS Score2
FROM 
(
	SELECT *
	FROM cteTest 
	WHERE RowNum = 1
	)AS A
LEFT OUTER JOIN
	cteTest AS B
	ON A.Name = B.Name
	AND A.RowNum + 1 = B.RowNum
LEFT OUTER JOIN
	cteTest AS C
	ON B.Name = C.Name
	AND B.RowNum + 1 = C.RowNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources