SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select on multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jack3r
Starting Member

Canada
4 Posts

Posted - 11/29/2012 :  14:33:34  Show Profile  Reply with Quote
Hi,

I'm not sure if is the good forum to ask this question, but I need help to write a SQL request. I use SQL Server Express 2008.

This is my test table (the real table have 85 Key columns and near 10000 rows)

TestId KeyA KeyB KeyC KeyD
1 111 222 333 444
2 555 666 777 888
3 555 000 000 222
4 111 333 444 888
5 111 222 111 333

I need to find out if a Key number is present on more than one line, no regards of the column. If it's unique or on the same line, I don't need it on the output

I'm looking for something like that:

Keys TestId
111 1
111 4
111 5
222 1
222 3
222 5
333 1
333 4
333 5
444 1
444 4
555 2
555 3
888 2
888 4


I try with unpivot to first get a list of all duplicated Key number, and them make some joints to filter the table, but I'm not sure is the good path...

Every help is welcome!

Jacques

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/29/2012 :  15:19:11  Show Profile  Reply with Quote
I would think that you should be able to unpivot and count the occurrences in ways that your business rules require. Based on my (limited) understanding of your rules, it can, for example, be done like shown below.

If having to manually list the key columns is not an acceptable solution, the only other thought that comes to mind is to write a dynamic query. However, I am a reluctant user of dynamic SQL - so I will defer that to someone else.
CREATE TABLE #tbl(TestId INT, keya CHAR(3), keyb CHAR(3), keyc char(3), keyd CHAR(3))
INSERT INTO #tbl VALUES 
('1','111','222','333','444'),
('2','555','666','777','888'),
('3','555','000','000','222'),
('4','111','333','444','888'),
('5','111','222','111','333')


SELECT DISTINCT kys,testid
FROM 
(
	SELECT 
		testId, kys, 
			COUNT(*) OVER (PARTITION BY testid,kys) N1,
			COUNT(*) OVER (PARTITION BY kys) N2
	FROM
		#tbl
	UNPIVOT (kys FOR k IN (keya, keyb, keyc, keyd))U
) s
WHERE N1 < N2 ORDER BY 1,2

DROP TABLE #tbl


________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page

Jack3r
Starting Member

Canada
4 Posts

Posted - 11/29/2012 :  23:23:27  Show Profile  Reply with Quote
Many thanks Elizabeth,

It is much clearer and direct than what I'm trying to do, and it's works perfectly.


Jacques
Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/30/2012 :  17:49:48  Show Profile  Reply with Quote
You are quite welcome!

________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000