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)
 Kind of a complicated query

Author  Topic 

Piemur1
Starting Member

10 Posts
Posted - 2014-11-10 : 11:19:22
Hello, I am trying to get a complicated calculated query result. I have 3 columns, an Umbrella System column, a System Name column, and a Flag boolean. Some systems do not have an Umbrella System, so the Flag is False for those records. Those that do will have values in the Umbrella System fields. Multiple Systems can be under that Umbrella System. What I want to do is have a calculated column for the first column listing ALL Umbrella Systems and all Non-Umbrella Systems.

For example:
Umbrella System | System | Flag
A Group | DOME | True
A Group | DOZ | True
B Group | FQDZZ | True
B Group | FOZ | True
NULL | HIZGH | False
J Group | CQDZZ | True
J Group | CDDP | True
NULL | LCMC | False

What I want the query to return is something like this:

A Group | DOME
A Group | DOZ
B Group | FQDZZ
B Group | FOZ
HIZGH | NULL
J Group | CDDP
J Group | CQDZZ
LCQC | NULL

Please help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-10 : 11:42:13
Do you mean something like this:


SELECT [Umbrella System], [System]
FROM mytable
WHERE Flag = 'True'
ORDER BY [Umbrella System], [System]
Go to Top of Page

Piemur1
Starting Member

10 Posts
Posted - 2014-11-10 : 11:58:42
Not exactly, no. Because that query will simply only display the Umbrella Systems and their Systems. The non-umbrella systems won't even be displayed. I am trying to get a composite of Umbrella Systems and Non-Umbrella Systems in the first column, then in the second column the Systems that fall underneath those Umbrella Systems.

So like the first column will have A Group, B Group, J Group, and HIZGH and LCQC (since those last two are non-umbrella systems). And in the second column will display DOME and DOZ for A Group, FQDZZ and FOZ for B Group, and CDDP and CQDZZ for J Group. Does that make sense?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-10 : 12:08:41
OIC, then try this:


SELECT case Flag 
		When 'True' then [Umbrella System] 
		Else [System]
	   END as [Umbrella System]
	   CASE Flag
	   		When 'True' then [System] 
       END as [System]

FROM mytable 
WHERE Flag = 'True'
ORDER BY [Umbrella System], [System]
Go to Top of Page

Piemur1
Starting Member

10 Posts
Posted - 2014-11-10 : 13:46:51
That did it. Had to kind of play with some of the things a bit, but I got the results I was looking for. Thank you!

I might have fibbed slightly when I was saying Flag was a boolean. It's actually a checkbox field that's generated by the system I use and stores the values as NULL for unchecked and 0 for checked. But it wasn't too difficult once I had an understanding of what CASE could do. Again, I greatly appreciate your assistance!

Here's what I actually ended up using (changing column names slightly as required for security).
SELECT CASE 
		WHEN Is_Umbrella_System = 0 THEN Umbrella_Group 
		ELSE System
	   END AS 'Umbrella Group',
	   CASE 
	   	WHEN Is_Umbrella_System = 0 THEN System
       END AS 'System'
FROM Table
ORDER BY Umbrella_Group, System


I'd noticed it wasnt working because you need a comma at the end of each of the CASE statements.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-11-10 : 14:29:05
Sorry about the missing commas :-}
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site