Combining two sets from same table into one set and being able to pull from new set.

Question

Combining two sets from same table into one set and being able to pull from new set.

thompson 44810

Grasshopper

Points: 19
More actions
August 10, 2011 at 8:36 am

#255364

What I am trying to do is pull data from two separate data sets (columns) from the same table and combine them in one column.
I have been able to do this part but where I am getting hung up is that I want to pull data from the new set or column.
Here is the statements that have gotten me to this point.
Step 1 – works
Use Name
Select certificate, ((convert(varchar(20),Street_Number)) + '' +
Street_Dir + ' ' +
Street_Name + ' ' +
Street_Type + ' ' +
Street_Dir_Suffix + ' ' +
Unit_Type + ' ' +
Apartment_Number + ' ' +
USPS_City_Name) From Votrm
where Status = 'A' and
Residence_Address <> ''
Step 2 works
Use Name
Select Residence_Address, Count (Residence_Address)
As Count From Votrm
Group By Residence_Address
Having (Count (Residence_Address) >1)
Order By Count Desc, Residence_Address
Need to combine, performing Step 2 on the results from Step 1

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

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

It is totally unclear what you are trying to do. Just taking a stab in the dark. You could modify your query to get the results you want in one statement instead of two. Or if you need to combine two result sets you need to look at the UNION operator. However, you will have to make the result sets have the same datatype and number of columns for a UNION.

_______________________________________________________________

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Try adding COUNT(*) OVER(PARTITION BY Residence_Address) to your first query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

ChrisM@Work (8/10/2011)
Try adding COUNT(*) OVER(PARTITION BY Residence_Address) to your first query.

From this, it sounds like you have managed to understand the initial post. I salute you sir 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Phil Parkin (8/10/2011)
ChrisM@Work (8/10/2011)
Try adding COUNT(*) OVER(PARTITION BY Residence_Address) to your first query.
From this, it sounds like you have managed to understand the initial post. I salute you sir 🙂

Heh Phil I think you confused me with someone else! Besides, it's still a guess...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Yeah, this post looks like the old puzzle to me:

"There were two crocodiles flying. One to the south but another one was of blue colour. Question: How many chairs in our local cinema"

The Right Answer:

"I don't need a fridge as I do not smoke"

So, go and figure....

May be OP wants this:

select Residence_Address, COUNT(*) as [Count]

FROM

(

Select certificate, ((convert(varchar(20),Street_Number)) + '' +

Street_Dir + ' ' +

Street_Name + ' ' +

Street_Type + ' ' +

Street_Dir_Suffix + ' ' +

Unit_Type + ' ' +

Apartment_Number + ' ' +

USPS_City_Name) as Residence_Address From Votrm

where Status = 'A'

) a

where Residence_Address <> ''

group by Residence_Address

having COUNT(*) > 1

order by [count] desc, Residence_Address

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

thompson 44810 Grasshopper Points: 19 More actions · Answer 6

Paul I understand this is really confusing on my first post.

What I want to do is pull and combine from two different columns in the same database to a temporary variable or temporary table. Then be able to pull from the new data set.

With both of the statements provided we can get the information into one column but are unable to pull from that new temporary column.

So my question is can a temp variable or temp table be used to pull data from and if so how.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

thompson 44810 (8/10/2011)
Paul I understand this is really confusing on my first post.
What I want to do is pull and combine from two different columns in the same database to a temporary variable or temporary table. Then be able to pull from the new data set.
With both of the statements provided we can get the information into one column but are unable to pull from that new temporary column.
So my question is can a temp variable or temp table be used to pull data from and if so how.

Can you post a few rows of expected output to help clarify your explanation?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

Paul?

Please provide sample source data & an idea of what the required results are (follow the link in my signature for advice on how best to ask for help).

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

thompson 44810 (8/10/2011)
...
So my question is can a temp variable or temp table be used to pull data from and if so how.

Ough, that is what you are asking, it is easy one.

Good News! Yes, temp variable or temp table can be used to pull data from.

Examples:

1. Pulling Data From Temp Table: SELECT * FROM #TempTableName

2. Pulling Data From Table Variable: SELECT * FROM @TempTableName

You can also specify the columns you want to return, filters and everything else as for the permanent tables. You can use them in JOINs too.

Hope it is clear now 😀

Do you want examples of how you can create and populate temp tables and table variables?:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

thompson 44810 Grasshopper Points: 19 More actions · Answer 10

Thanks Eugene,

We understand the temp table and table variable. What we would like is to create a temp variable from the process that we can pull from.

Is this possible?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

thompson 44810 (8/10/2011)
...What we would like is to create a temp variable from the process that we can pull from...

What are you really trying to do? How about that representative result set? It's highly likely that given an adequate explanation of what you're trying to achieve, the optimal way of coding it is completely different to your current thinking. Give Eugene the benefit of the doubt, he knows a thing or two about TSQL 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

thompson 44810 Grasshopper Points: 19 More actions · Answer 12

Eugene thank you.

We understand the temp table and table variable creation. Our quandary is can a temp variable be created and can that be used to pull data from?

We are trying to do this without creating a separate table.

Thanks

Phil Parkin SSC Guru Points: 247180 More actions · Answer 13

Please explain why you want to use a 'temp variable' rather than a table variable. What do you think the difference is?

thompson 44810 Grasshopper Points: 19 More actions · Answer 14

Well our thoughts are if a separate table is created then we take into possibility that more potential issues for problems may arise. Therefore if we use the temp variable that is create within the statement and are able to pull from the same statement we would use less overhead.

Am I way off?