Combining two seperate tables into one

Hello!
I don`t know how to do some query. I have two tables which looks like
it:
First table:
MRPC 200504 200505 200506
C01 1 2 3
C02 2 3 4
C03 3 3 2
Second table:
MRPC 200504 200505 2000506
C01 20% 20% 50%
C02 10% 30% 70%
C03 30% 40% 15%
I would like to combine these two tables into one table, which would
look like it:
MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
C01 1 20% 2 20% 3 50%
C02 2 10% 3 30% 4 70%
C03 3 30% 3 40% 2 15%
The number of columns is changeable, because once a w there is extra
column added. Is it possible to link these two tables and create one
score table? As you can see the second table has the same columns as
first one and don`t have a string "PRC" in the name of column.
Thank you for your help
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***>> Is it possible to link these two tables and create one score table?
Yes it is possible, but unless you are working towards achieving some
performance benefits (for instance, by materializing data) for specific
queries, such an attempt is of little use. You can always derive the
resultset using a simple JOIN -- in many cases a view should be the
solution.
Keeping them separate, on the other hand, allows you to manipulate data in
each table separately without affecting the other.
It is not a show-stopper, just the matter of aliasing the column names.
Anith|||On Fri, 05 Aug 2005 09:05:05 -0700, Marcin Zmyslowski wrote:

>
>Hello!
>I don`t know how to do some query. I have two tables which looks like
>it:
>First table:
>MRPC 200504 200505 200506
>C01 1 2 3
>C02 2 3 4
>C03 3 3 2
>Second table:
>MRPC 200504 200505 2000506
>C01 20% 20% 50%
>C02 10% 30% 70%
>C03 30% 40% 15%
>I would like to combine these two tables into one table, which would
>look like it:
>MRPC 200504 200504 PRC 200505 200505 PRC 200506 200506 PRC
>C01 1 20% 2 20% 3 50%
>C02 2 10% 3 30% 4 70%
>C03 3 30% 3 40% 2 15%
>
>The number of columns is changeable, because once a w there is extra
>column added. Is it possible to link these two tables and create one
>score table? As you can see the second table has the same columns as
>first one and don`t have a string "PRC" in the name of column.
Hi Marcin,
Instead of adding columns to your tables for each w, why not add one
column to the table to hold the w, then just add rows?
The first table would look like this:
MRPC W Data
C01 200504 1
C01 200505 2
C01 200506 3
C02 200504 2
C02 200505 3
C02 200506 4
C03 200504 3
C03 200505 3
C03 200506 2
The second table would be similar. Depending on actual business
requirements, it might also be possible to combine these two tables:
MRPC W Data PRC
C01 200504 1 20%
C01 200505 2 20%
C01 200506 3 50%
C02 200504 2 10%
C02 200505 3 30%
C02 200506 4 70%
C03 200504 3 30%
C03 200505 3 40%
C03 200506 2 15%
(BTW, what datatype do you use for the percentages?)
Tables with a seperate column for each w/month/year/whatever tend to
bring lots of probles and no gain.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi!
I could have data in rows, but I can only do a crosstab query which let
me create one columns (wk), not two columns at the same time: "wk" and
"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
still don`t know how to combine these two tables into one. Could you
give me a code example' I cannot find it in archieve. I would be very
grateful for help.
Thanx, Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 08 Aug 2005 00:54:37 -0700, Marcin Zmyslowski wrote:

>Hi!
>I could have data in rows, but I can only do a crosstab query which let
>me create one columns (wk), not two columns at the same time: "wk" and
>"wk prc", that`s, why I really need data (WK and WK prc) in columns. I
>still don`t know how to combine these two tables into one. Could you
>give me a code example' I cannot find it in archieve. I would be very
>grateful for help.
>Thanx, Marcin from Poland
Hi Marcin,
Doing a cross tab is actually better handled by the presentation tier.
But if there is no way that the client can handle this and you must do
it server side, use something like this untested code:
SELECT MRPC,
MAX(CASE WHEN W = '200504' THEN Data END) AS '200504',
MAX(CASE WHEN W = '200504' THEN PRC END) AS '200504 PRC',
MAX(CASE WHEN W = '200505' THEN Data END) AS '200505',
MAX(CASE WHEN W = '200505' THEN PRC END) AS '200505 PRC'
FROM YourTable
GROUP BY MRPC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

Combining two fields into one

I have a small problem.
In my query I am calling a field of status and a field of statusyearcount. I
need to get these into one cell in a table, called statusyearcount.
ie. Status = C
StatusYearCount = 10
Need to show C10 in the table.
I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
getting an "incorrect format string".
Im gathering it because one is an int and the other varchar. Can somebody
please point me in the right direction here.
Thanks muchly.You can cast the Field.
=Fields!Status.Value + Int(Fields!StatusYearCount.Value)
--
| Thread-Topic: Combining two fields into one
| thread-index: AcWo+8ArbHZHrGD+TI2fppOvcbIgrg==| X-WBNR-Posting-Host: 202.175.143.143
| From: "=?Utf-8?B?TmF0IEpvaG5zb24=?="
<NatJohnson@.discussions.microsoft.com>
| Subject: Combining two fields into one
| Date: Wed, 24 Aug 2005 15:32:46 -0700
| Lines: 18
| Message-ID: <3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51089
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a small problem.
|
| In my query I am calling a field of status and a field of
statusyearcount. I
| need to get these into one cell in a table, called statusyearcount.
|
| ie. Status = C
| StatusYearCount = 10
|
| Need to show C10 in the table.
|
| I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I
keep
| getting an "incorrect format string".
|
| Im gathering it because one is an int and the other varchar. Can
somebody
| please point me in the right direction here.
|
| Thanks muchly.
|
||||This will do it:
=Fields!Status.Value & CStr(Fields!StatusYearCount.Value)
CStr converts integer to string, allowing succesful concatenation.
GeoSynch
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com...
>I have a small problem.
> In my query I am calling a field of status and a field of statusyearcount. I
> need to get these into one cell in a table, called statusyearcount.
> ie. Status = C
> StatusYearCount = 10
> Need to show C10 in the table.
> I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
> getting an "incorrect format string".
> Im gathering it because one is an int and the other varchar. Can somebody
> please point me in the right direction here.
> Thanks muchly.
>

Combining the results of a cursor loop

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuileSET NOCOUNT ON
CREATE TABLE #CurrentRates
(
AccountType VARCHAR(50),
EffectiveDate DATETIME,
tier INT,
gross FLOAT,
net FLOAT,
aer FLOAT,
footnotes VARCHAR(2000),
[id] INT
)
GO

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT #CurrentRates
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData

SELECT * FROM #CurrentRates

Works, but is it good? ;)

Regards

Shaun McGuile|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?|||Works, but is it good? ;) It uses cursors :o|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?

The line

...Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s...

Splits the ids into a table of column product id.

Have I understood your question?

Regards|||I have and always will be the one and only Shaun McGuile ;)

Lost my dbforums password/email combination somehow.|||Forgot to add the 'drop table #CurrentRates' at the end of the code

Doh!|||Beg your pardon - I thought you were parsing as a string.

Ok - what's the pk of dbo.GetProductRateView?|||pk ha ha ha ha ha ha ha - er..sorry Pootle you had me there.

The db has no pk's nor integrity of any type its real bad

dbo.GetProductRateView is a View pulling data from three non normalised tables its really evil - your heart and that of other members of the community might not take the shock of seeing them.

Its like 'The Ring' of databases (like the film - you see it then you die) lol.|||Well brace yourself

SELECT TOP 100 PERCENT dbo.saving_product.name AS AccountType, dbo.saving_product_variant.from_date AS EffectiveDate,
dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, dbo.saving_product_variant.net, dbo.saving_product_variant.aer,
dbo.saving_date.footnotes, dbo.saving_product.id
FROM dbo.saving_product INNER JOIN
dbo.saving_product_variant ON dbo.saving_product.id = dbo.saving_product_variant.link_id INNER JOIN
dbo.saving_date ON dbo.saving_product.id = dbo.saving_date.link_id
GROUP BY dbo.saving_product.name, dbo.saving_product_variant.from_date, dbo.saving_product_variant.tier, dbo.saving_product_variant.gross,
dbo.saving_product_variant.net, dbo.saving_product_variant.aer, dbo.saving_date.footnotes, dbo.saving_product.id
ORDER BY MAX(dbo.saving_product_variant.date_id) DESC|||date_id? :S

Are you getting the most recent row based on the value date_id? If so then you should know that order by clauses are not guaranteed to work in views. Better to create a view with no order by clause and order it when required.

From BoL:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.|||What version are you running BTW?|||INSERT #CurrentRates
SELECT TOP 1 v.*
FROM ( SELECT * FROM dbo.GetProductRateView Order By date_id desc) as v
WHERE v.[id] = @.Id

and remove the order by clause from the view?

Regards

Shaun McGuile|||You can do - no need for the inner query BTW. I'm thinking more than that though.

What version are you running?|||SQLServer 2000 is the db.|||Heh - turns out I didn't need it - apols.

SELECT v.*
FROM dbo.GetProductRateView as v
INNER JOIN--"Last" date per product.
(SELECT dbo.saving_product.id
, MAX(dbo.saving_product_variant.date_id) AS last_date_id
FROM dbo.saving_product
INNER JOIN
dbo.saving_product_variant
ON dbo.saving_product.id = dbo.saving_product_variant.link_id
INNER JOIN
(SELECT *
FROM dbo.split_function(@.IdsString)) AS ids
ON ids.Value = dbo.saving_product.id
GROUP BY dbo.saving_product.id) AS last_prods
ON last_prods.id = v.id
AND last_prods.last_date_id = v.date_id
How is that for the data?|||I'll give it a go and let you know.

Cheers Pootle.

Haven't looked in on Yak Coral in ages. Might do it today if I get time.|||Yeah that works splen-diddly (its how you say it out loud that gets ya).

Only modifictions I had to make were field name for the productID instead of value, altered the view to return date_id field and a DISTINCT is needed as in
SELECT DISTINCT v.* ...

Bril thats my homework done! On with the next assignment!

lol only joking! I dont do homework!

Cheers Pootle

Shaun McGuile|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 seconds

Amazing!|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 secondsThat's set based programming for you. The other thing to remember is that speed of the cursor will be linear. Each additional iteration will take ~ as long as the last. Set based stuff mitigates against changes in scale much better.

Combining strings

Let's assume we have two tables - Customers and Orders.

I need a query that will return a string value containing a list of order titles from the Orders table for a particular customer.

How can this be done?

Thanks.

Hi vkh,

you have to use a function approach for this, as it can be seen on (sort of, I would vary this one to a temporary table rather than a cursor, but just to show you the iterative approach)

http://www.sqlteam.com/item.asp?ItemID=2368

HTH; jens Suessmeyer.

|||Thank you!

Combining results in Comma delimitered strings

I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?DECLARE @.commadelimitedthisisanannoyingstringname VARCHAR(8000)

SELECT @.thatstringupthereyouregoingtonottypethis = ''

SELECT @.String = PersonId + ', ' + @.String
FROM Person

SELECT LEFT(@.String, LEN(@.String)-1)

or something like that|||Thanks, that seems to work,... I didn't think it would but some how it does...|||Thanks, that seems to work,... I didn't think it would but some how it does...

It should have worked. :) Why would you not think it would, just curious.|||I would have thought that for that structure to work there would have had to be some sort of loop or something...

I thought I had seen it done using some other function like coalesce or something but when I read the help it didn't look right...

Hmmm,... actually thinking about it now it makes sense... basically it ignores what if selected in the table select and just selects the final built up string...

Seems a little inefficient, is there a better way??|||Actually, if you do a search here or on www.sqlteam.com, you'll find all kinds of ways to do it. :) You can do it with a function using COALESCE. I think for a single string though, this one is pretty efficient.|||Come on, derrick, NOBODY here thought that would work the first time we saw it months back! It's probably one of the most popular tricks on the forum!|||?? You're joking right? I have scripts going way back using that.|||I know that folks use the living stuff out of this construct, but it shouldn't work. It actually violates the SQL-92 standard, since the column value is derived iteratively. My guess is that once the standards committee realizes that this flaw exists in a widely used SQL dialect, they'll add a test for it to the test suite and shortly thereafter Microsoft will either eliminate the behavior or make it switch dependant.

-PatP|||Yup, it's a nifty little thing that has gotchya's people don't think about, and as always tend to overuse it. Like with everything else that is non-standard, when it comes to an end, a lot of these guys are gonna be screaming bloody hell, but it's only because they were lazy at the beginning, though not lazy enough to prevent it right there, and have a beer ;)|||Yeah, but it is lots of fun to watch them running around, screaming to the four winds when the database engine behavior is fixed and their code breaks. As you are fond of observing, those who ignore history are doomed to repeat it!

-PatP|||Okie then guys,... what is the right way to do it? To be honest I have actually implemented a different solution as I didn't get a reply in time so it isn't going to cause me any problems but I would be interested in knowing if there is a proper way or not...|||The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||Well that answer gains zero points for usefullness...

No offense meant. For the purposes of the project I am doing I need a comma delimited string of all the id's. This is not for displaying but for internal processing. A string may not be the best option but at this point it time is the most flexible and the project at hand. The truth is that there is no front in for the problem I am working on. It's a logic problem that I want to solve. Once it is solved I might decide that it is no use or I might decide that there is a front end requirement but at this point there is not.

Now while I agree that I probably don't want SQL to return a comma delimited string because it is a misrepresentation of the actual data I do want to know what is the best way of getting sql to return a comma delimited string so I know what I am talking about when I rule it out as an option.|||No matter how the string is getting formed, it's still limited to the total length of 8000 character (wonder how many times I tried to hint it?:rolleyes: )

Why don't you tell us what the app is for, and why it needs a comma-delimited listing of PersonIDs? Someone (maybe me) would be able to come up with an alternative, hey?! ;)|||I think the answer to your hint question is once (in this post anyway).

The string is used for determining individual branches in a family tree. The processing is all done using asp/vbscript. An array might be better then a string but because you are shuffling data into and out of other stings/arrays at this point a string is the best contruct to test the theory and determine it's usefulness.

At this point conversion of a recordset to a string is simplier then a recordset to an array.

The actually processing overhead of using a string is probably higher but until I determine the usefulness of the function their is little point moving to an array which would be more complex at this point.

Before you ask, yes, you are stuck with asp and vbscript. It can't go to a vb component and it can't upgrade to asp.Net.

Knock yourself out.|||Hey-hey-hey, with that kind of attitude, YOU knock yourself out, not me, alright?! I was just trying to help you (which is why I choose to post here), but as you MIGHT have noticed, it's NOT my problem. If you think you're better than that, - KNOCK YOURSELF OUT, do yourself a favor.

EDITED: and BTW, I hinted on VARCHAR limitation at least twice. I simply didn't think that such an obvious thing as this needs to be mentioned...Obviously it does, hey?!|||*sigh* why is it that people are easily offended...

Look, I appreciate what you are saying and I appreciate your input, the "knock yourself out" line is a standard line from where I come from to say "go for it and good luck". I'm sorry it offended you.|||Fair dinkum. :cool:|||WHAT WAS WRONG WITH THIS ANSWER? Tel me straight, please, I I promise I will take a serious consideration over how I post my answers...The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||What's wrong with the answer is it doesn't actually answer the question.

The question is, is there a proper way to create a comma delimited string in the manner described within SQL (and if there is, what is it).

It is not, how should I build my application so that I do not have to use a comma delimited string, nor is it what restrictions will I put in place by using a comma delimited string.

I'd be quite happy if some one answered "there is no 'proper' way to generate a comma delimited string from sql". I am aware that there are going to be limitations, but in some cases (not this one) the limitation may not come into play (depending on data and data structures).|||Hey, have it your way. I hope the the limitation pointed out is not gonna be a problem for you ;)|||Eventually it would be, but since this was only to test a theory it's not going to and like I said previously I have actually gone about it a different way, but it would still be interesting to know if there is a "proper" way to do it...

An example of where it might be useful is when you want a comma delimited list of the months that are stored in a particular table. You can be certain that the length of the string will not exceed 8000 characters...

I'm not sure where else it might be useful but there are bound to be others.

Thanks for your input.sqlsql

Combining results from 2 databases in 1 table

Hi, I hope someone will take a moment to answer my question.
I'm combining results from 2 databases. The SQL statement works fine in
Query Analyser using the full names of the database tables (db1.dbo.table1,
db2.dbo.table1). In SRS the dataset executes correctly under the data tab
when using the master table as the shared datasource.
However in the layout tab I dont have access to any of the fields in my
dataset.
Can anyone help me display the contents of the dataset in SRS.
Many thanks for taking the time to answer this.
SteveB.Have you tried clicking the refresh button on the data tab?
"fisab" wrote:
> Hi, I hope someone will take a moment to answer my question.
> I'm combining results from 2 databases. The SQL statement works fine in
> Query Analyser using the full names of the database tables (db1.dbo.table1,
> db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> when using the master table as the shared datasource.
> However in the layout tab I dont have access to any of the fields in my
> dataset.
> Can anyone help me display the contents of the dataset in SRS.
> Many thanks for taking the time to answer this.
> SteveB.|||Silly oversight of mine - thanks for the help.
Is this the best method of getting data from 2 databases (on the same
server)? I assume I'd only use a linked server if the databases were on
different machines.
"David Siebert" wrote:
> Have you tried clicking the refresh button on the data tab?
> "fisab" wrote:
> > Hi, I hope someone will take a moment to answer my question.
> >
> > I'm combining results from 2 databases. The SQL statement works fine in
> > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > when using the master table as the shared datasource.
> > However in the layout tab I dont have access to any of the fields in my
> > dataset.
> >
> > Can anyone help me display the contents of the dataset in SRS.
> > Many thanks for taking the time to answer this.
> > SteveB.|||As far as I know, that's the best way to get data from both databases. You
might post to the sql discussions group and probably get a more informed
response there.
"fisab" wrote:
> Silly oversight of mine - thanks for the help.
> Is this the best method of getting data from 2 databases (on the same
> server)? I assume I'd only use a linked server if the databases were on
> different machines.
> "David Siebert" wrote:
> > Have you tried clicking the refresh button on the data tab?
> >
> > "fisab" wrote:
> >
> > > Hi, I hope someone will take a moment to answer my question.
> > >
> > > I'm combining results from 2 databases. The SQL statement works fine in
> > > Query Analyser using the full names of the database tables (db1.dbo.table1,
> > > db2.dbo.table1). In SRS the dataset executes correctly under the data tab
> > > when using the master table as the shared datasource.
> > > However in the layout tab I dont have access to any of the fields in my
> > > dataset.
> > >
> > > Can anyone help me display the contents of the dataset in SRS.
> > > Many thanks for taking the time to answer this.
> > > SteveB.

Combining PIVOT and INSERT queries

Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]As Q10FROM (SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]WHERE SurveyID=1)AS preData PIVOT (MAX(Answer)FOR QuestionIDIN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )AS dataORDER BY RespondantID

Thanks,

Martin

You can use a CTE and a SELECT into to get your pivot result to a new table. You need to remove ORDER BY RespondantID clause first.

Here is the sql script.

WITH mycte

AS

(SELECT RespondantID, [1]AS Q1, [2]AS Q2, [3]AS Q3, [4]AS Q4, [5]AS Q5, [6]AS Q6, [7]AS Q7, [8]AS Q8, [9]AS Q9, [10]AS Q10

FROM(SELECT RespondantID, QuestionID, Answer

FROM [3_Temp]

WHERE SurveyID= 1)AS preDataPIVOT(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))AS data

)

SELECT RespondantID, [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [Q9], [Q10]INTO [NewtableResult]FROM mycte

combining 2 rows

Hello,
I have a query that returns 2 rows, which I need to combine into 1 row.
The query looks like this:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
...the 2 rows that the query returns are:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 NULL 173.27
ClientA ClientA EUR 69 3465.29 NULL
...and I want to combine those 2 rows into a single row that looks like
this:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 3465.29 173.27
Thanks in advance,
Craig H.Use your query as a derived table and collapse the rows in a query around
it -- like this:
Select CName, AName, ACurrency, Payments, Sum( [Amount (incl. commission)]),
Sum ([Commission])
From
(
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
) T
Group By CName, AName, ACurrency, Payments
Order By CName, AName, ACurrency
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Craig H." <spam@.thehurley.com> wrote in message
news:u2%23N1$OnFHA.3336@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.|||hi
it might work, if u remove
t.TCode from the group by clause
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Craig H." wrote:

> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.
>|||> it might work, if u remove
> t.TCode from the group by clause
No. It will give you the error t.TCCode is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3485024E-D8DF-42DE-A9F5-13C37C0B22FF@.microsoft.com...
> hi
> it might work, if u remove
> t.TCode from the group by clause
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Craig H." wrote:
>|||Looks like you just need to remove TCode from your GROUP BY list:
...
GROUP BY t.CName, t.AName, t.ACurrency
David Portas
SQL Server MVP
--|||Good catch. I missed it too. So put the CASE expression inside the SUM
aggregate. Something like the following, depending on the desired
result of the SUM:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
SUM(CASE WHEN t.TCode = 'debit_batch'
THEN t.LAmount/t.ExchangeRate END) AS 'Amount (incl. commission)',
SUM(CASE WHEN t.TCode = 'commission'
THEN t.LAmount/t.ExchangeRate END) AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency
ORDER BY t.CName, t.AName, t.ACurrency ;
David Portas
SQL Server MVP
--|||David,
Correct me If I am wrong.
As per my understanding , removing TCode fro the Group By list will
throw the error
TCCode is invalid in the select list because it is not contained in
either an aggregate function or
the GROUP BY clause.
SELECT pub_id,
CASE WHEN type = 'business' THEN SUM(ytd_sales) END
FROM Titles
GROUP By pub_id
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
> Looks like you just need to remove TCode from your GROUP BY list:
> ...
> GROUP BY t.CName, t.AName, t.ACurrency
> --
> David Portas
> SQL Server MVP
> --
>|||Never mind. I had seen the other post only after sending this.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23iPyJWPnFHA.764@.TK2MSFTNGP14.phx.gbl...
> David,
> Correct me If I am wrong.
> As per my understanding , removing TCode fro the Group By list will
> throw the error
> TCCode is invalid in the select list because it is not contained in
> either an aggregate function or
> the GROUP BY clause.
>
> SELECT pub_id,
> CASE WHEN type = 'business' THEN SUM(ytd_sales) END
> FROM Titles
> GROUP By pub_id
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
>

Combinig fields

Hi all,
In Access I can do a simply query and put something like:
SELECT FirstName & ", " & LastName AS FullName FROM People
doing this woud combine the two fields into one... can this be done in a SQL
stored procedure?
Thanks
GavUse the + operator to combine columns...just make sure that you don't =
add numbers if you really want to combine them as strings. =20
use pubs
go
select au_lname, au_fname, 'FullName' =3D au_lname + ', ' + au_fname =
From authors
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>=20
> In Access I can do a simply query and put something like:
>=20
> SELECT FirstName & ", " & LastName AS FullName FROM People
>=20
> doing this woud combine the two fields into one... can this be done in =
a SQL
> stored procedure?
>=20
> Thanks
> Gav
>=20
>|||I have tried this but it simply returns null all the time.
Regards
Gav
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
Use the + operator to combine columns...just make sure that you don't add
numbers if you really want to combine them as strings.
use pubs
go
select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
authors
Keith
"Gav" <spam@.spam.com> wrote in message
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> In Access I can do a simply query and put something like:
> SELECT FirstName & ", " & LastName AS FullName FROM People
> doing this woud combine the two fields into one... can this be done in a
SQL
> stored procedure?
> Thanks
> Gav
>|||I can see whats happening, if one of the fields is null it only returns
null... can I get it to ignore the field if it is null?
Regards
Gav
"Gav" <spam@.spam.com> wrote in message
news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have tried this but it simply returns null all the time.
> Regards
> Gav
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
> Use the + operator to combine columns...just make sure that you don't add
> numbers if you really want to combine them as strings.
> use pubs
> go
> select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
> authors
> --
> Keith
>
> "Gav" <spam@.spam.com> wrote in message
> news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> SQL
>|||There are a few options that you can use...
Here are a few that come to mind:
CREATE TABLE #foo (col1 char(5), col2 char(5))
INSERT INTO #foo (col1, col2) VALUES ('test', null)
INSERT INTO #foo (col1, col2) VALUES ('test1', 'test1')
GO
SELECT col1 + ' ' + col2 FROM #foo=20
SELECT col1 + ' ' + ISNULL(col2, '') FROM #foo
SELECT col1 + ' ' + COALESCE(col2, '') FROM #foo
SELECT col1 + ' ' + CASE WHEN col2 IS NULL THEN '' ELSE col2 END FROM =
#foo
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only =
returns
> null... can I get it to ignore the field if it is null?
>=20
> Regards
> Gav
>=20
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
don't add
From
done in a
>=20
>|||You can replace that column value with an empty string:
SELECT
au_fname + COALESCE(initial, '') + au_lname AS full_name
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gav" <spam@.spam.com> wrote in message
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only returns
> null... can I get it to ignore the field if it is null?
> Regards
> Gav
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
add
a
>|||Thanks for the help Keith and Tibor thats works great.
Cheers
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU0HchECEHA.3400@.tk2msftngp13.phx.gbl...
> You can replace that column value with an empty string:
> SELECT
> au_fname + COALESCE(initial, '') + au_lname AS full_name
> FROM tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Gav" <spam@.spam.com> wrote in message
> news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> add
From
in
> a
>

Combined result...

hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column?
example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.well, daimous, it seems like you did not understand why i moved your previous thread to the microsoft SQL Server forum

so here is the SQL answer --select street||City||postal_code as address
from yourtableif you find that this doesn't work in SQL Server, i trust it will bring to your attention that SQL Server questions should be posted in the SQL Server forum and not the SQL forum

:)|||Try this

Select [street]+', '+[city]+' '+[postal_code] as Address
from YourTable

This assumes that you have [postal_code] defined as a varchar, and not an integer or numeric field. I put in some spaces and a comma, so your output would be something like this:

Street, City Postal_Code|||If you have NULL values in your table and are using default SQL Server settings, you may need to use this:

Select coalesce([street]+', ', '')+Coalesce([city]+' ', '')+Coalesce([postal_code], '') as Address
from YourTable

Now, go open up Books Online and read about concatenation and the COALESCE function.

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks Binu
The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>
|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index no
t
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

Combine two queries - help please

I have a table that has two dates in it, a date opened and a date
closed. I would like to create one query to give me the number of
records that have been opened each month plus, and this is the hard
part the number of those records that have been closed each month. I
can get the result with two seperate queries but have been unable to
get it combined into one query with three values for each month, i.e.,
the month, the number opened and the number of those that were opened
in the month that have been subsequently closed.

Here's my two queries. If anyone can help I'd appreciate.

SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
WHERE (DateClosed IS NOT NULL)
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),
3) + '
' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]
FROM table
GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,
DateOpened), 3)
+ ' ' + CAST(YEAR(DateOpened) AS Char(5))
ORDER BY CONVERT(CHAR(7), DateOpened, 120)

TIA

BillTry:

SELECT MIN(dateopened),
COUNT(*),
COUNT(dateclosed)
FROM YourTable
GROUP BY YEAR(dateopened), MONTH(dateopened)

--
David Portas
SQL Server MVP
--|||David;

Thank you very much that works just fine. I appreciate the help.

Cheers;

Bill

Combine record

Hi guys..
is there any query to do this action:
i want to combine view record into a single record.
exm.

table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...

into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

thx before..dede (neolempires2@.gmail.com) writes:

Quote:

Originally Posted by

is there any query to do this action:
i want to combine view record into a single record.
exm.
>
table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...
>
>
into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

Check out http://www.projectdmx.com/tsql/rowconcatenate.aspx for
suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Combine multiple lines for one record into one comma-delimted field

Here is a query I have: It's long, so bear with me.
Scroll to the bottom to see what question I have, please.
This is a SQL 2003 db. In the select statement I am only asking for 2
fields which in reality, I am asking for about 40 (hence the long FROM
section)
SELECT v_basic_booking_data_ODBC.ptt_last_name,
dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
FROM dbo.v_basic_case_rec_data INNER JOIN
dbo.v_CRA_BO_01_Journaling_Data
v_CRA_BO_01_Journaling_Data ON
dbo.v_basic_case_rec_data.cr_urn =
v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
dbo.v_CRA_cpt_code ON
dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
JOIN
reportuser.v_BKA_02_Booking_Data
v_BKA_02_Booking_Data INNER JOIN
dbo.v_basic_booking_data_ODBC
v_basic_booking_data_ODBC ON
v_BKA_02_Booking_Data.book_urn =
v_basic_booking_data_ODBC.book_urn INNER JOIN
dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
JOIN
dbo.book_audref book_audref ON
v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
v_CRA_BO_01_Journaling_Data.cr_urn =
book_audref.cr_urn
WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
'2006-03-10 00:00:00', 102)) AND
(v_basic_booking_data_ODBC.book_date <
CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
ORDER BY v_basic_booking_data_ODBC.room_descr,
v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
I get results like
lname cptcode
DOE 1111111
DOE 4343445
SMITH 5456544
RALF 4789008
what I want is for Doe's line to read
DOE 111111,4343445
How can I do this with my extensive query above?
Thank you.
*** Sent via Developersdex http://www.examnotes.net ***Joe , since you have mot posetd DDL+ sample data I did some testing on my
own. Just be aware that the below approach isn't reliable and shoud be
avoided ,instead do such reports on the client side
create table w
(
id int not null,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||In my experience of trying it, dynamic columns in SQL is horrible, and
I've only achieved it with some messy Dynamic SQL. What are you
outputting to? if it's going out to a web app or something then I'd
suggest using the c# (or whatever language) to clean up your result set
there. SQL is more geared to obtaining rather than formatting data.
Cheers
Will|||http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
I would get the following result
Locations
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?
SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.posting.google.c om...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?
|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com... [vbcol=seagreen]
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
ID=2,Location1=Boston,Location2=NewYork,Location3= London,Location4=Paris
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message[vbcol=seagreen]
>news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com.. .
column of
>ID=2,Location1=Boston,Location2=NewYork,Location3 =London,L
ocation4=Paris[vbcol=seagreen]
location1,location2
>
>.
>
sqlsql

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loca
tion4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.postin
g.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Lo
cation4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loc
ation4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>
ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Location4=Paris[vbcol=s
eagreen]
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
> news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
column of[vbcol=seagreen]
> ID=2,Location1=Boston,Location2=NewYork,
Location3=London,L
ocation4=Paris
location1,location2[vbcol=seagreen]
>
>.
>

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.posting.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:
>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
> >I have a table Venues
> >ID int
> >Location1 char(10),
> >Location2 char(10),
> >Location3 char(10),
> >Location4 char(10)
> >
> >and would like to have a query that returns a single column of
> >Locations i.e
> >for the record
ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
> >I would get the following result
> >
> >Locations
> >--
> >Boston
> >NewYork
> >London
> >Paris
> >
> >Is it possible to merge the values from columns location1,location2
> >etc into a new column?
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
>news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com...
>> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>> >I have a table Venues
>> >ID int
>> >Location1 char(10),
>> >Location2 char(10),
>> >Location3 char(10),
>> >Location4 char(10)
>> >
>> >and would like to have a query that returns a single
column of
>> >Locations i.e
>> >for the record
>ID=2,Location1=Boston,Location2=NewYork,Location3=London,L
ocation4=Paris
>> >I would get the following result
>> >
>> >Locations
>> >--
>> >Boston
>> >NewYork
>> >London
>> >Paris
>> >
>> >Is it possible to merge the values from columns
location1,location2
>> >etc into a new column?
>> SELECT Location1 AS Locations
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location2
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location3
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location4
>> FROM Venues
>> WHERE ID = 2
>>
>> By the way, your design is not properly normalized. The
>> Venue-Locations should be in a seperate table.
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>.
>

Combine 2 rows from derived table into 1 row w/o repeating query?

I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name

2 1 "A"

2 2 "B"

I want to get the following:

ID Name1 Name2

2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

Here it is,

Code Snippet

Create Table #data (

[ID] int ,

[Status] int ,

[Name] Varchar(100)

);

Insert Into #data Values('2','1','A');

Insert Into #data Values('2','2','B');

Select

Id

,max(case when Status=1 Then [Name] end) [name1]

,max(case when Status=2 Then [Name] end) [name2]

from

(

Select * from #data -- Your Derived Table

) as Data

Group By

Id

|||

The solution will work, I just needed to think about how to expand it for more columns, but I got it now.

If it's very very slow, I will try something with a CTE - I think that'll work as well.

|||

The CTE was 50-60% faster than the other route! but that method is also useful if using sql2000.|||

Yeah a CTE is going to be the way to go on this in 2005 for sure. Never been a fan of using temp tables and I avoid them when I can. So here's my own variation on the above sample...

Code Snippet

SELECT
[ID],
max(case when Status=1 Then [Name] end) [name1],
max(case when Status=2 Then [Name] end) [name2]

from (
SELECT 2 As [ID], 1 As Status, 'A' As [Name]
UNION SELECT 2, 2, 'B'
) AS Data
GROUP BY [ID]