SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Creating the Union Query for a List Type Control
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 10/12/2012 :  10:32:00  Show Profile  Reply with Quote
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

Edited by - sergeant_time on 10/12/2012 12:02:12

lazerath
Constraint Violating Yak Guru

USA
342 Posts

Posted - 10/12/2012 :  10:44:43  Show Profile  Reply with Quote
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.
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
342 Posts

Posted - 10/12/2012 :  12:47:57  Show Profile  Reply with Quote
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.
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 10/16/2012 :  10:19:05  Show Profile  Reply with Quote
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
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
342 Posts

Posted - 10/17/2012 :  16:13:20  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 10/17/2012 :  16:33:05  Show Profile  Reply with Quote
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
Go to Top of Page

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 10/22/2012 :  11:57:15  Show Profile  Reply with Quote
@ 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000