Select Values from multiple Rows Into a Single Row

Question

Post reply

Select Values from multiple Rows Into a Single Row

dbaforever

Hall of Fame

Points: 3740
More actions
April 27, 2009 at 2:36 pm

#131523

hi all,
I have a table which contains a date column and a column that contains a numerical value....only 1 record for each date:
(varchar) (smalldatetime) (Int)
DatabaseName Date Qty
Db1 3/1/2009 00:00:00 2501
Db1 4/1/2009 00:00:00 3722
Db2 3/1/2009 00:00:00 1488
Db2 4/1/2009 00:00:00 1754
one record for the 1st day of each month for each DatabaseName
My objective is to select the Quantities for presentation
in an Excel worksheet... but place the quantities across the line for each database.
How can I construct the Select statement so that it selects all quantities for a database name with the results like this:
Db1 2501 3722
Db2 1488 1754
I know it's fairly easy, just dont remember how I did this a few years ago.
Thanks very much.
John

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jetro Ten Centuries Points: 1257 More actions · Answer 1

Hi,

I played around with your question. Unfortunately I don't know an easy solution...but this one could work for you.

declare @dates varchar(1000) = ''

select @dates = @dates + ',' + '[' + convert(varchar,date,112) +']'

from

(

select distinct date from dbs order by date

) a

DECLARE @sql nvarchar(1000)

set @sql =

'select * from dbs

PIVOT

(

MAX(qty)

FOR date in (' + substring(@dates,2,LEN(@dates)) + ')

) p

'

exec sp_executesql @sql

Hope this helps 🙂

-----------------------
SQL Server Database Copy Tool at Codeplex

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

John,

Do the numbers have to be in separate columns, or just on the same line?

Also, why do you need to do this type of denormalization? Where is the output going to be used?

--Jeff Moden

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

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

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

dbaforever Hall of Fame Points: 3740 More actions · Answer 3

Jeff,

I will (manually is ok) copy the resultset into an Excel sheet.

Tracking database sizes first day of each month.

Ideally need the sizes (Qty) to be in separate columns.

Thanks, John

Edward Boyle-478467 Ten Centuries Points: 1165 More actions · Answer 4

Elegant solution on Post http://www.sqlservercentral.com/Forums/Topic704553-145-1.aspx

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

John Bates (4/28/2009)
Jeff,
I will (manually is ok) copy the resultset into an Excel sheet.
Tracking database sizes first day of each month.
Ideally need the sizes (Qty) to be in separate columns.
Thanks, John

In that case, I'd likely put the data into an EAV and only pivot (or cross tab) it when needed.

--Jeff Moden

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

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

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

dbaforever Hall of Fame Points: 3740 More actions · Answer 6

Thanks so much for taking the time to reply, everyone.

Jetro, I tried your code and had several errors, had to move on.

Edward, I will try the web page you recommended - thanks.

Jeff, your code may be the most promising... - but what does EAV mean?

John