multiple column evaluation returning 1 column ..

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 2000 Forums

SQL Server Development (2000)

multiple column evaluation returning 1 column ..

Author

Topic

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 19:29:37

Hi all,

I have a need/want to simplify some lengthy queries...amongst the tasks is taking a source table with 16 possible columns and returning the result into 4 columns, with four different criteria. and example of one column/criteria is below (the 2nd column would be for "B" etc...).

the table result is only about 6 or 7 columns, but the t-sql is 6 pages of this kind of thing...and the conditionals in the column selection are separated in the where clause by OR's...

sample code below


Create table foobar 
(ID int identity(1,1)
,C1 char(1) null
,C2 char(1) null
,C3 char(1) null
,C4 char(1) null
,C5 char(1) null
,C6 char(1) null
,C7 char(1) null
,C8 char(1) null
,C9 char(1) null
,C10 char(1) null
,C11 char(1) null
,C12 char(1) null)

Insert into foobar
Select 'A','A',	'',	'',	'',	'',	'',	'',	'',	'',	'',	'' UNION
Select 'B','A','A',	'',	'',	'',	'',	'',	'',	'',	'',	'' UNION
Select 'C','B','B',	'',	'',	'',	'',	'',	'',	'',	'',	'' UNION
Select '',	'',	'','A',	'',	'',	'',	'',	'',	'',	'',	'' UNION
Select '',	'',	'',	'','A','A',	'',	'',	'',	'',	'',	'' UNION
Select '',	'',	'',	'','B','A','A',	'',	'',	'',	'',	'' UNION
Select '',	'',	'',	'','C','B','B',	'',	'',	'',	'',	'' UNION
Select '',	'',	'',	'',	'',	'',	'','A',	'',	'',	'',	'' UNION
Select '',	'',	'',	'',	'',	'',	'',	'','A','A',	'',	'' UNION
Select '',	'',	'',	'',	'',	'',	'',	'','B','A','A',	'' UNION
Select '',	'',	'',	'',	'',	'',	'',	'','C','B','B',	'' UNION
Select '',	'',	'',	'',	'',	'',	'',	'',	'',	'',	'','A'

Go
Select ID, 
	   ColA = Case 'A' When C1 then C1 
					   When C2 then C2
					   When C3 then C3
					   When C4 then C4
					   When C5 then C5
					   When C6 then C6
					   When C7 then C7
					   When C8 then C8
					   When C9 then C9
					   When C10 then C10
					   When C11 then C11
					   When C12 then C12
		end

FROM Foobar
Where ( Case 'A' When C1 then C1 
					   When C2 then C2
					   When C3 then C3
					   When C4 then C4
					   When C5 then C5
					   When C6 then C6
					   When C7 then C7
					   When C8 then C8
					   When C9 then C9
					   When C10 then C10
					   When C11 then C11
					   When C12 then C12
		end) = 'A'

This does what is required, but there has to be an easier way to return the below?


ID	ColA
1	A 
2	A 
3	A 
5	A 
6	A 
7	A 
9	A 
10	A 
11	A

Any ideas? The actual query I inherited to rewrite is much more complicated, but this column selection method is used on 4 columns, with 4 different criteria, against 3 datasets...looking to simplify it if possible...

(this isn't so bad, just trying to see if there is a better way)

Poor planning on your part does not constitute an emergency on my part.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-02 : 22:06:58

Try this:


SELECT ID,
       CASE WHEN LEN(C) > LEN(REPLACE(C, 'A', '')) THEN 'A' END AS ColA
  FROM (SELECT ID,
               ISNULL(C1, '') + ISNULL(C2, '') + ISNULL(C3, '') + ISNULL(C4, '') +
               ISNULL(C5, '') + ISNULL(C6, '') + ISNULL(C7, '') + ISNULL(C8, '') +
               ISNULL(C9, '') + ISNULL(C10, '') + ISNULL(C11, '') + ISNULL(C12, '') AS C
          FROM dbo.foobar) AS T
 WHERE LEN(C) > LEN(REPLACE(C, 'A', ''));

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 22:31:27

Thanks! Hadn't thought of just concatonating the columns. I will try it out with the actual data..here is part two..which complicates. your suggesting will help me clean up colA, but ColB is similar, although it returns the value from a corresponding tandem columns. In reality there are 16 of each, but this shows the principal.

Note in this case colC1-6 and cols D1-6. If C? = 'A' then return D? where ? is the numerical incremental of the column as below..not sure if the isnull bit will work but welcome any thoughts.


Create table foobar 
(ID int identity(1,1)
,C1 char(1) null
,C2 char(1) null
,C3 char(1) null
,C4 char(1) null
,C5 char(1) null
,C6 char(1) null
,D1 char(1) null
,D2 char(1) null
,D3 char(1) null
,D4 char(1) null
,D5 char(1) null
,D6 char(1) null)
Insert into foobar
Select 'A','A',	'',	'',	'',	'',	'X','',	'S',	'',	'',	'' UNION
Select 'B','A','A',	'',	'',	'',	'X','S','',	'',	'',	'' UNION
Select 'C','B','B',	'A','',	'',	'',	'',	'T','X',	'',	'' UNION
Select '',	'',	'', 'A','',	'',	'',	'',	'',	'T',	'',	'' UNION
Select '',	'',	'',	'','A','A',	'',	'',	'',	'',	'X',	'' UNION
Select '',	'',	'',	'','B','A','A',	'',	'',	'',	'',	'X' UNION
Select '',	'',	'',	'','C','B','B',	'',	'',	'',	'',	'' UNION
Select '',	'',	'',	'',	'',	'',	'','',	'',	'',	'',	'' UNION
Select '',	'',	'A','',	'',	'',	'',	'','X','',	'',	'' UNION
Select '',	'',	'',	'A','',	'',	'',	'','','X','',	'' UNION
Select '',	'',	'',	'',	'A','',	'',	'','','','X',	'' UNION
Select '',	'',	'B','',	'C','A','',	'',	'',	'',	'','Z'

Select * FROM Foobar
Go
Select 
ID 
,ColA = Case 'A' When C1 then C1 
					   When C2 then C2
					   When C3 then C3
					   When C4 then C4
					   When C5 then C5
					   When C6 then C6
					 
		end
,ColB = Case 'A' When C1 then D1 
					   When C2 then D2
					   When C3 then D3
					   When C4 then D4
					   When C5 then D5
					   When C6 then D6
					   	end

FROM Foobar
Where (Case 'A' When C1 then C1 
					   When C2 then C2
					   When C3 then C3
					   When C4 then C4
					   When C5 then C5
					   When C6 then C6
					   
		end) = 'A' 


Drop table foobar

/*
results
ID	ColA	ColB
2	A	X
3	A	X
4	A	X
6	A	T
7	A	X
8	A	X
9	A	Z
10	A	X
11	A	S
12	A	X */

Poor planning on your part does not constitute an emergency on my part.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-03 : 06:38:47

Try this:


SELECT ID,
       CASE WHEN CHARINDEX('A', C) > 0 THEN 'A' END AS ColA,
       CASE WHEN CHARINDEX('A', C) > 0 THEN SUBSTRING(D, CHARINDEX('A', C), 1) END AS ColB
  FROM (SELECT ID,
               ISNULL(C1, '') + ISNULL(C2, '') + ISNULL(C3, '') +
               ISNULL(C4, '') + ISNULL(C5, '') + ISNULL(C6, '') AS C,
               ISNULL(D1, '') + ISNULL(D2, '') + ISNULL(D3, '') +
               ISNULL(D4, '') + ISNULL(D5, '') + ISNULL(D6, '') AS D
          FROM dbo.foobar) AS T
 WHERE CHARINDEX('A', C) > 0;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources