Creating the Union Query for a List Type Control

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)

Creating the Union Query for a List Type Control

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-10-12 : 10:32:00

I created a union query for a list type control for one of my reports. I would like to update this queary to combine the FPCC and CCP vice listing them seperately. Below is copy of the union queury that I used to create my list.

SELECT '8' AS Split, 'FPCC' AS PilotName, 1 AS OrderID
UNION
SELECT '28' AS Split, 'FPCC' AS PilotName, 1 AS OrderID
UNION
SELECT '3' AS Split, 'CCP' AS PilotName, 1 AS OrderID
UNION
SELECT '19' AS Split, 'PCC VISN 17' AS PilotName, 4 AS OrderID
UNION
SELECT '43' AS Split, 'PCC VISN 4' AS PilotName, 5 AS OrderID
UNION
SELECT '44' AS Split, 'PCC VISN 6' AS PilotName, 6 AS OrderID
UNION
SELECT '45' AS Split, 'PCC VISN 2' AS PilotName, 7 AS OrderID
UNION
SELECT '46' AS Split, 'PCC VISN 1' AS PilotName, 8 AS OrderID
UNION
SELECT '47' AS Split, 'PCC VISN 22 LONG BEACH' AS PilotName, 9 AS OrderID
UNION
SELECT '49' AS Split, 'PCC VISN 11- Danville' AS PilotName, 11 AS OrderID
UNION
SELECT '66' AS Split, 'PCC VISN 22 LOMA LINDA' AS PilotName, 12 AS OrderID
ORDER BY OrderID

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-12 : 10:44:43

Please follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

At the very least, it would help to see what output you are expecting.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-12 : 12:47:57

I see you edited your existing post, but did not provide the expected output. In order for us to help you, it is important that you make an attempt to help us.

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-10-16 : 10:19:05

I would like to combine the 'CPP' with 'FPCC' into one.

SELECT '8' AS Split, 'FPCC' AS PilotName, 1 AS OrderID
UNION
SELECT '3' AS Split, 'CCP' AS PilotName, 1 AS OrderID
UNION
SELECT '19' AS Split, 'PCC VISN 17' AS PilotName, 4 AS OrderID
UNION
SELECT '43' AS Split, 'PCC VISN 4' AS PilotName, 5 AS OrderID
UNION
SELECT '44' AS Split, 'PCC VISN 6' AS PilotName, 6 AS OrderID
UNION
SELECT '45' AS Split, 'PCC VISN 2' AS PilotName, 7 AS OrderID
UNION
SELECT '46' AS Split, 'PCC VISN 1' AS PilotName, 8 AS OrderID
UNION
SELECT '47' AS Split, 'PCC VISN 22 LONG BEACH' AS PilotName, 9 AS OrderID
UNION
SELECT '49' AS Split, 'PCC VISN 11- Danville' AS PilotName, 11 AS OrderID
UNION
SELECT '66' AS Split, 'PCC VISN 22 LOMA LINDA' AS PilotName, 12 AS OrderID
ORDER BY OrderID

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-17 : 16:13:20

Provide a [SELECT ... UNION ...] statement that will produce the result you want. There are many ways to combine things and combining the way to "combine 'CPP' with 'FPCC'" is not obvious. Do you want a comma separated string like 'CPP,FPCC'? How do you want to reconcile the Split field? Is that supposed to be added together? Also comma separated? MIN / MAX?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-17 : 16:33:05

here I put them together for ya:

SELECT '83' AS Split, 'FPCCCCP' AS PilotName, 1 AS OrderID
UNION
SELECT '19' AS Split, 'PCC VISN 17' AS PilotName, 4 AS OrderID
UNION
SELECT '43' AS Split, 'PCC VISN 4' AS PilotName, 5 AS OrderID
UNION
SELECT '44' AS Split, 'PCC VISN 6' AS PilotName, 6 AS OrderID
UNION
SELECT '45' AS Split, 'PCC VISN 2' AS PilotName, 7 AS OrderID
UNION
SELECT '46' AS Split, 'PCC VISN 1' AS PilotName, 8 AS OrderID
UNION
SELECT '47' AS Split, 'PCC VISN 22 LONG BEACH' AS PilotName, 9 AS OrderID
UNION
SELECT '49' AS Split, 'PCC VISN 11- Danville' AS PilotName, 11 AS OrderID
UNION
SELECT '66' AS Split, 'PCC VISN 22 LOMA LINDA' AS PilotName, 12 AS OrderID
ORDER BY OrderID

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2012-10-22 : 11:57:15

@ lazerath,

I understand there are many ways to combine the two splits together. I am adding split 3 and 8 together. So instead of selecting CCP and FPCC in the drop list in the report, I just want to select FPCC and it will consolidate both CCP and FPCC together. This is the website I ran into http://www.wiseowl.co.uk/blog/s110/union-query-list-control.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources