Select different coumns based on one column value

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 different coumns based on one column value

Author

Topic

dkekesi
Starting Member

38 Posts

Posted - 2011-08-05 : 05:44:48

Hi All,

How can I achieve to do a different select based on a column value. I am selecting for XML.
Example:
If column1=1 then
begin
select col2, col3
end
If column1=2 then
begin
select col4, col5
end
etc...

Is it even possible?
Thanks for the help.

Daniel

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-05 : 06:45:12

I'm not totally sure what you are asking for but I think you can use CASE

something like:


DECLARE @sample TABLE (
	[index] INT
	, [switcher] CHAR(1)
	, [val1] TINYINT
	, [val2] TINYINT
	, [val3] TINYINT
	, [val4] TINYINT
	)

INSERT @sample VALUES
	  (1, 'A', 0, 1, 2, 3)
	, (2, 'A', 7, 5, 2, 1)
	, (3, 'B', 5, 3, 1, 9)
	, (4, 'B', 1, 1, 1, 1)

SELECT * FROM @sample

SELECT
	[index]
	, [switcher]
	, CASE [switcher] WHEN 'A' THEN [val1] WHEN 'B' THEN [val3] END AS [SwitchedValue1]
	, CASE [switcher] WHEN 'A' THEN [val2] WHEN 'B' THEN [val4] END AS [SwitchedValue2]
FROM
	@sample

Results:



(4 row(s) affected)
index       switcher val1 val2 val3 val4
----------- -------- ---- ---- ---- ----
1           A        0    1    2    3
2           A        7    5    2    1
3           B        5    3    1    9
4           B        1    1    1    1

(4 row(s) affected)

index       switcher SwitchedValue1 SwitchedValue2
----------- -------- -------------- --------------
1           A        0              1
2           A        7              5
3           B        1              9
4           B        1              1

(4 row(s) affected)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dkekesi
Starting Member

38 Posts

Posted - 2011-08-05 : 11:05:04

Thanks Charlie,
What I wish to do is select different columns based on the value of a single column.
Example: if col1 has value 1 then I want to select col2 and col3. OTOH if col1=2 then I want to select col4 and col 5.
Hope this makes more sense.

Thanks again.
Daniel

Best Regards,
Daniel

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-05 : 11:12:16

So that's exactly what I've posted

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dkekesi
Starting Member

38 Posts

Posted - 2011-08-05 : 13:54:53

quote:
Originally posted by Transact Charlie

So that's exactly what I've posted

I do not think that's what you posted. It must be me and English not being my native language. I may not be able to explain it any better but I give it another shot.
Your solution returns different static data based on the value of a column. If column value is say 1 vthen return static value, else return another static value. But it's not just static data that I want to return based on the test condition. I want a completely different "select" to execute.

Best Regards,
Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources