October 6, 2014 at 9:03 am
Hello,
I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension,
Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned. Sorry if this is elementary, but I haven't figured it out, and have tried many things.
Agent_ID Agent_ID2
11111 22222
<code>
SELECT DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID
when agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
</code>
October 6, 2014 at 9:15 am
I'm not 100% sure of what you are after, but maybe start with this:
SELECT DISTINCT
Extension = isNull(Agent_Id,'') + isNull(Agent_Id2,'')
from [AA_Helper].[dbo].[tblEmpData]
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 6, 2014 at 9:18 am
No...that just concatenates the 2 I want it to create it as a second row
11111
22222
33333
October 6, 2014 at 9:28 am
I created one cte, and then union all the select of agent_ID2..Like this
;with
csServiceExtAgentID(Extension) as
(SELECT DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
)
SELECT * FROM csServiceExtAgentID where extension is not null
union all
SELECT DISTINCT
CASE WHEN agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
where agent_id2 is not null
October 6, 2014 at 11:31 am
Still didn't work...Not getting all of the agent_id2's
October 6, 2014 at 12:10 pm
cbrammer1219 (10/6/2014)
I created one cte, and then union all the select of agent_ID2..Like this
;with
csServiceExtAgentID(Extension) as
(SELECT DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
)
SELECT * FROM csServiceExtAgentID where extension is not null
union all
SELECT DISTINCT
CASE WHEN agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
where agent_id2 is not null
What is the point of the case expressions here? In the first query if AGENT_ID is null it will return NULL. But then later you select them only where it isn't null. Why not drop the case expression and use a where clause to only return those rows where it isn't null.
The second query is even more redundant. You have a case expression around agent_id2 so that when it is not null it returns agent_id2 and a where clause that only returns where they are not null. The case expression in both queries is not needed.
_______________________________________________________________
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/
October 6, 2014 at 12:12 pm
Try this
use AA_Helper
go
select extension = AGENT_ID
from dbo.tblEmpData
where agent_ID is not null
union all
select agent_id2
from dbo.tblEmpData
where agent_ID2 is not null
If you want DISTINCT entries only, change UNION ALL to UNION.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 6, 2014 at 12:14 pm
Something like this if I understand what you are after.
SELECT AGENT_ID
FROM [AA_Helper].[dbo].[tblEmpData]
where AGENT_ID is not null
group by AGENT_ID
union all
SELECT agent_id2
FROM [AA_Helper].[dbo].[tblEmpData]
where agent_id2 is not null
group by agent_id2
Now unless you include another column in these you have no way to keep each "group" together. If you need that you would need to add another column from tblEmpData so you can order by that column.
_______________________________________________________________
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/
October 6, 2014 at 1:46 pm
Because there is also a extension field, sorry guys copied the wrong query. I am trying to put extension, agent_ID and agent_ID into extension, sometimes one extension will have more than one (extension) so they were put into agent_ID and agent_ID2 so I am trying to create a drop down to display the extension, agent_ID and agent_ID. Hope that makes sense.
;with
csServiceExtAgentID(Extension,Department) as
(SELECT
DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID
WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT
FROM [AA_Helper].[dbo].[tblEmpData]
where extension is not null and LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'
)
SELECT * FROM csServiceExtAgentID
;with
csServiceExtAgentID2(Extension,Department) as
(SELECT DISTINCT
CASE WHEN agent_id2 is not null then agent_id2
WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT
FROM [AA_Helper].[dbo].[tblEmpData]
where agent_id2 is not null and LEN(extension) > 3
)
SELECT * FROM csServiceExtAgentID2
October 6, 2014 at 1:56 pm
cbrammer1219 (10/6/2014)
Because there is also a extension field, sorry guys copied the wrong query. I am trying to put extension, agent_ID and agent_ID into extension, sometimes one extension will have more than one (extension) so they were put into agent_ID and agent_ID2 so I am trying to create a drop down to display the extension, agent_ID and agent_ID. Hope that makes sense.
;with
csServiceExtAgentID(Extension,Department) as
(SELECT
DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID
WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT
FROM [AA_Helper].[dbo].[tblEmpData]
where extension is not null and LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'
)
SELECT * FROM csServiceExtAgentID
;with
csServiceExtAgentID2(Extension,Department) as
(SELECT DISTINCT
CASE WHEN agent_id2 is not null then agent_id2
WHEN EXTENSION is not null then EXTENSION end as extension, DEPARTMENT
FROM [AA_Helper].[dbo].[tblEmpData]
where agent_id2 is not null and LEN(extension) > 3
)
SELECT * FROM csServiceExtAgentID2
Still the case expressions don't make sense here. The second especially. There is no chance that anything other than the first condition can be met in your second query because you filter out the other possibilities with your where predicates.
Your questions is still extremely vague but I am taking one more shot in the dark.
with csServiceExtAgentID(Extension,Department) as
(
SELECT
DISTINCT
ISNULL(AGENT_ID, Extension) as extension
, DEPARTMENT
FROM [AA_Helper].[dbo].[tblEmpData]
where LEN(ISNULL(AGENT_ID, Extension)) > 3 and ISNULL(AGENT_ID, Extension) not like '6%' and DEPARTMENT like 'Service%'
)
,
csServiceExtAgentID2(Extension,Department) as
(
SELECT DISTINCT
agent_id2
, DEPARTMENT
FROM [AA_Helper].[dbo].[tblEmpData]
where agent_id2 is not null
and LEN(extension) > 3
)
SELECT * FROM csServiceExtAgentID
UNION ALL
SELECT * FROM csServiceExtAgentID2
If that doesn't do it you need to provide some details. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
October 6, 2014 at 2:14 pm
I just did this..
select distinct
extension
from tblEmpData
where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'
union all
select distinct
case
when agent_id is not null then agent_id
end as extension
from tblEmpData
where LEN(agent_id) > 3 and agent_id not like '6%' and DEPARTMENT like 'Service%'
UNION ALL
select distinct
case
when agent_id2 is not null then agent_id2
end as extension
from tblEmpData
where LEN(agent_id2) > 3 and agent_id2 not like '6%' and DEPARTMENT like 'Service%'
ORDER BY EXTENSION
October 6, 2014 at 2:24 pm
cbrammer1219 (10/6/2014)
I just did this..
select distinct
extension
from tblEmpData
where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'
union all
select distinct
case
when agent_id is not null then agent_id
end as extension
from tblEmpData
where LEN(agent_id) > 3 and agent_id not like '6%' and DEPARTMENT like 'Service%'
UNION ALL
select distinct
case
when agent_id2 is not null then agent_id2
end as extension
from tblEmpData
where LEN(agent_id2) > 3 and agent_id2 not like '6%' and DEPARTMENT like 'Service%'
ORDER BY EXTENSION
So aside from continuing to use pointless case expressions did you get it figured out or do you still need help?
_______________________________________________________________
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/
October 6, 2014 at 2:27 pm
Yes I did, and the example you provided did not help, but thanks for your attempt.
October 6, 2014 at 2:35 pm
cbrammer1219 (10/6/2014)
Yes I did, and the example you provided did not help, but thanks for your attempt.
Cool. Glad that you figured out a solution. You could simplify your query to this.
select distinct
extension
from tblEmpData
where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'
union all
select distinct
agent_id
from tblEmpData
where LEN(agent_id) > 3 and agent_id not like '6%' and DEPARTMENT like 'Service%'
UNION ALL
select distinct
agent_id2
from tblEmpData
where LEN(agent_id2) > 3 and agent_id2 not like '6%' and DEPARTMENT like 'Service%'
ORDER BY EXTENSION
_______________________________________________________________
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/
October 6, 2014 at 2:44 pm
Maybe you're looking to unpivot your data?
CREATE TABLE tblEmpData(
Agent_ID int,
Agent_ID2 int)
INSERT INTO tblEmpData VALUES
(11111, 22222),
(33333, NULL),
(NULL, 44444),
(NULL, NULL)
SELECT x.extension
FROM tblEmpData
CROSS APPLY (VALUES(Agent_ID), (Agent_ID2)) x(extension)
WHERE extension IS NOT NULL
SELECT x.extension
FROM tblEmpData t
UNPIVOT (extension FOR agent IN( Agent_ID, Agent_ID2)) x
GO
DROP TABLE tblEmpData
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply