Remove a value from a union

Question

Remove a value from a union

Meatloaf

SSCrazy

Points: 2564
More actions
June 23, 2015 at 3:15 pm

#301965

Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Meatloaf (6/23/2015)
Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.

It is not clear what you are trying to do here. Your query as posted won't work. The first query has no table and the number of columns in the two queries are inconsistent.

Why not just join to your list of letters? Or better yet, get the first character of each last name from the table.

SELECT LastName, FirstName, Dept, Phone, left(LastName, 1) as firstLetter

from someTable

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Meatloaf (6/23/2015)
Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.

You could insert the results of the first query into a temp table, #Temp

Then something like:

SELECT LastName, FirstName, Dept, Phone

FROM #Temp1

UNION ALL

SELECT DISTINCT LEFT(LastName, 1), NULL, NULL, NULL

FROM #Temp1

ORDER BY 1, 2, 3, 4

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Assuming you have a table GivenNames with a column Name.

select distinct CA.Name

from GivenNames GN

cross apply (

select left(GN.Name,1) as Name

union all

select GN.Name

) CA

order by CA.Name

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Meatloaf (6/23/2015)
Any help is appreciated.

Yep. I agree. Please see the link titled "How to post code problems" in my signature line below. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Alvin Ramard (6/23/2015)
Meatloaf (6/23/2015)
Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.
You could insert the results of the first query into a temp table, #Temp
Then something like:
SELECT LastName, FirstName, Dept, Phone
FROM #Temp1
UNION ALL
SELECT DISTINCT LEFT(LastName, 1), NULL, NULL, NULL
FROM #Temp1
ORDER BY 1, 2, 3, 4

~~Careful now. Don't forget that ORDER BY ordinal has been deprecated~~.

{EDIT} This is bad information. Please see my post later in this thread that explains why. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

Jeff Moden (6/23/2015)
Alvin Ramard (6/23/2015)
Meatloaf (6/23/2015)
Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.
You could insert the results of the first query into a temp table, #Temp
Then something like:
SELECT LastName, FirstName, Dept, Phone
FROM #Temp1
UNION ALL
SELECT DISTINCT LEFT(LastName, 1), NULL, NULL, NULL
FROM #Temp1
ORDER BY 1, 2, 3, 4
Careful now. Don't forget that ORDER BY ordinal has been deprecated.

Deprecated? Why didn't I know that? Oh yes. I'm stuck on older versions of SQL Server.

I never use ordinal positions for production. I've been know to use it often with ad-hoc queries though. I started to use the OP's ORDER BY statement but I saw his column names didn't match what was in the query, so I switched to the easy way out.

Ok, here's the corrected code:

SELECT

LastName

,FirstName

,Dept

,Phone

FROM #Temp1

UNION ALL

SELECT

DISTINCT LEFT(LastName, 1) AS [LastName]

,NULL AS [FirstName]

,NULL AS [Dept]

,NULL AS [Phone]

FROM #Temp1

ORDER BY LastName, FirstName, Dept, Phone

Thanks Jeff.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Alvin Ramard (6/24/2015)
Jeff Moden (6/23/2015)
Alvin Ramard (6/23/2015)
Meatloaf (6/23/2015)
Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.
You could insert the results of the first query into a temp table, #Temp
Then something like:
SELECT LastName, FirstName, Dept, Phone
FROM #Temp1
UNION ALL
SELECT DISTINCT LEFT(LastName, 1), NULL, NULL, NULL
FROM #Temp1
ORDER BY 1, 2, 3, 4
Careful now. Don't forget that ORDER BY ordinal has been deprecated.
Deprecated? Why didn't I know that? Oh yes. I'm stuck on older versions of SQL Server.
I never use ordinal positions for production. I've been know to use it often with ad-hoc queries though. I started to use the OP's ORDER BY statement but I saw his column names didn't match what was in the query, so I switched to the easy way out.
Ok, here's the corrected code:
SELECT
LastName
,FirstName
,Dept
,Phone
FROM #Temp1
UNION ALL
SELECT
DISTINCT LEFT(LastName, 1) AS [LastName]
,NULL AS [FirstName]
,NULL AS [Dept]
,NULL AS [Phone]
FROM #Temp1
ORDER BY LastName, FirstName, Dept, Phone
Thanks Jeff.

You don't have to give the columns in the second query an alias. In fact, you could name them whatever you want because the column names in the result set always come from the first query when using UNION. 😉 It does however provide clarity and it probably how I would write it.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

...
Sean Lange (6/24/2015)
Alvin Ramard (6/24/2015)
Jeff Moden (6/23/2015)
Careful now. Don't forget that ORDER BY ordinal has been deprecated.
Deprecated? Why didn't I know that? Oh yes. I'm stuck on older versions of SQL Server.
I never use ordinal positions for production. I've been know to use it often with ad-hoc queries though. I started to use the OP's ORDER BY statement but I saw his column names didn't match what was in the query, so I switched to the easy way out.
Ok, here's the corrected code:
SELECT
LastName
,FirstName
,Dept
,Phone
FROM #Temp1
UNION ALL
SELECT
DISTINCT LEFT(LastName, 1) AS [LastName]
,NULL AS [FirstName]
,NULL AS [Dept]
,NULL AS [Phone]
FROM #Temp1
ORDER BY LastName, FirstName, Dept, Phone
Thanks Jeff.
You don't have to give the columns in the second query an alias. In fact, you could name them whatever you want because the column names in the result set always come from the first query when using UNION. 😉 It does however provide clarity and it probably how I would write it.

I was aware of that Sean. I just like to include them to make it easier to see which columns align with which. In a short query like this one it doesn't matter. In queries with many columns it can be a big help if you need to make changes.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Meatloaf SSCrazy Points: 2564 More actions · Answer 9

Hi, Thank you doing the union with distinct is working. What I would like to display is the letter as a header with names following. If there is no names with a specific letter than omit the letter.

For example:

A

Apple, Bob

Apricot, Jane

C

Carrot,John

Corn, Jon

E

Eggplant, Ed

"B" is missing becuase there is no name with the last name starting with "B", so "B" is off the list.

Is there a way to create this formatting?

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

Meatloaf (6/24/2015)
Hi, Thank you doing the union with distinct is working. What I would like to display is the letter as a header with names following. If there is no names with a specific letter than omit the letter.
For example:
A
Apple, Bob
Apricot, Jane
C
Carrot,John
Corn, Jon
E
Eggplant, Ed
"B" is missing becuase there is no name with the last name starting with "B", so "B" is off the list.
Is there a way to create this formatting?

Isn't that what your first query was doing?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

Alvin Ramard (6/24/2015)
Deprecated? Why didn't I know that? Oh yes. I'm stuck on older versions of SQL Server.

Ah... hang on a minute while I get the egg off my face.

I read about ORDER BY ordinal being deprecated on the official MS deprecation list (IIRC) for 2005 years ago. I couldn't figure out why someone like you wouldn't know that such a widely used feature had been deprecated so I went back in all the deprecation lists from 2005 through 2012 and quit. I then went to ORDER BY in 2016 (https://msdn.microsoft.com/en-us/library/ms188385.aspx) and here's what is says...

order_by_expression
Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name or column alias, [font="Arial Black"]or a nonnegative integer representing the position of the column in the select list[/font].

There are also NO deprecation warnings on that page.

This isn't the first time that I've read a supposedly final deprecation list and then find out that they had changed it and I don't think to look again even years later.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Meatloaf SSCrazy Points: 2564 More actions · Answer 12

Hi Alvin,

It is close to the first query. What is different is my first query will show all letters regardless if there is a lastname.

For example, my current query shows the follwing:

A

Apple, Bob

Apricot, Jane

B

C

Carrot,John

Corn, Jon

E

Eggplant, Ed

F

G

Grape,Sam

H

I

J

K

L

Lettuce, Lou

...

Steve Jones - SSC Editor SSC Guru Points: 736260 More actions · Answer 13

Quick drive by, but what about using a CASE with the letter to check if there is something returned form the second query, return the letter. Otherwise, return a space?

sgmunson SSC Guru Points: 110640 More actions · Answer 14

Meatloaf (6/23/2015)
Hi,
I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.
For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.
SELECT LastName, FirstName, Dept, Phone
UNION ALL
SELECT v.letter,NULL,NULL,NULL,NULL
FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)
ORDER BY vchLastName, vchFirstName
Any help is appreciated.

How about this:

SELECT LastName, FirstName, Dept, Phone

FROM SomeTable

UNION ALL

SELECT DISTINCT UPPER(LEFT(LastName, 1)), NULL, NULL, NULL

FROM SomeTable

ORDER BY LastName, FirstName

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)