Select on multiple columns - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Select on multiple columns

Author

Topic

Jack3r
Starting Member

4 Posts

Posted - 2012-11-29 : 14:33:34

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

39 Posts

Posted - 2012-11-29 : 15:19:11

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.

Jack3r
Starting Member

4 Posts

Posted - 2012-11-29 : 23:23:27

Many thanks Elizabeth,

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

Jacques

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-30 : 17:49:48

You are quite welcome!

________________________________________
-- Yes, I am indeed a fictional character.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources