How to merge rows into colum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to merge rows into colum

Author

Topic

timlisten
Starting Member

26 Posts

Posted - 2012-06-08 : 16:57:32

Hi, I have a table with the following columns

name, date, price1, price2
A, 7/14, 1.1, 2.2
A, 7/15, 3.2, 4.4
B, 7/14, 4.3, 5.5
B, 7/15, 3.5, 6.6

Let's say the date is always going to be 7/14 and 7/15
What's the fastest way to merge the date for each name so that I have the following

name, price1_14, price2_14, price1_15, price2_15
A, 1.1, 2.2, 3.2, 4.4
B, 4.3, 5.5, 3.5, 6.6

I can write the query but the performance is not that well. I need one with really good performance as I have a huge database that I need to run it quickly.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 02:10:06

do you want it to repeat for each of date values present? then use below

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-09 : 05:43:20

If the Dates won't change then you can use a Static Pivot as follows:


--Creating Tables

Create Table Ex
(name char(1),
 date varchar(10), 
 price1 float, 
 price2 FLOAT )


--Inserting Sample Data

Insert Into Ex
Select 'A', '7/14', 1.1, 2.2
Union ALL
Select 'A', '7/15', 3.2, 4.4
Union ALL
Select 'B', '7/14', 4.3, 5.5
Union ALL
Select 'B', '7/15', 3.5, 6.6


--Pivot

Select a.name, MAX(a.Price1_14) As Price1_14, Max(b.Price2_14) As Price2_14, MAX(a.Price1_15) As Price1_15, MAX(b.Price2_15) As Price2_15 From
	(Select Name, [7/14] As Price1_14, [7/15] As Price1_15 From Ex
	Pivot
	(Max(Price1) For Date In ([7/14], [7/15])) As pvt) As a
	JOIN
	(Select Name, [7/14] As Price2_14, [7/15] As Price2_15 From Ex
	Pivot
	(Max(Price2) For Date In ([7/14], [7/15])) As pvt) As b ON a.name = b.name
Group By a.name

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 11:20:41

quote:

Originally posted by vinu.vijayan

If the Dates won't change then you can use a Static Pivot as follows:


--Creating Tables

Create Table Ex
(name char(1),
 date varchar(10), 
 price1 float, 
 price2 FLOAT )


--Inserting Sample Data

Insert Into Ex
Select 'A', '7/14', 1.1, 2.2
Union ALL
Select 'A', '7/15', 3.2, 4.4
Union ALL
Select 'B', '7/14', 4.3, 5.5
Union ALL
Select 'B', '7/15', 3.5, 6.6


--Pivot

Select a.name, MAX(a.Price1_14) As Price1_14, Max(b.Price2_14) As Price2_14, MAX(a.Price1_15) As Price1_15, MAX(b.Price2_15) As Price2_15 From
	(Select Name, [7/14] As Price1_14, [7/15] As Price1_15 From Ex
	Pivot
	(Max(Price1) For Date In ([7/14], [7/15])) As pvt) As a
	JOIN
	(Select Name, [7/14] As Price2_14, [7/15] As Price2_15 From Ex
	Pivot
	(Max(Price2) For Date In ([7/14], [7/15])) As pvt) As b ON a.name = b.name
Group By a.name

N 28° 33' 11.93148"
E 77° 14' 33.66384"

No need of pivotting twice. You can use below method


SELECT name,
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_15
FROM table
GROUP BY name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timlisten
Starting Member

26 Posts

Posted - 2012-06-09 : 20:47:18

Thanks guys, the dates does change, but I can get the dates as variables and pass them in.

visakh16, the method doesn't seem to work as the price1_14 shows the same data as price1_15 and price2_14 is the same as price2_15.

SELECT name,
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_15
FROM table
GROUP BY name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 21:08:08

quote:
Originally posted by timlisten

Thanks guys, the dates does change, but I can get the dates as variables and pass them in.

visakh16, the method doesn't seem to work as the price1_14 shows the same data as price1_15 and price2_14 is the same as price2_15.

SELECT name,
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/15' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/15' THEN price2 END) AS price2_15
FROM table
GROUP BY name

there was a typo
see modified suggestion above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timlisten
Starting Member

26 Posts

Posted - 2012-06-10 : 01:01:34

Hi visakh16, I figured there was a typo in your original post, so the modified version was the one I tried and they showed the same data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-10 : 01:36:30

nope they wont. show your full query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timlisten
Starting Member

26 Posts

Posted - 2012-06-10 : 02:11:15

I used this one

SELECT name
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/15' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/15' THEN price2 END) AS price2_15
FROM table
GROUP BY name

The reason I think when you use the group by name, everything is merge into one column that's why it didn't work.
If I use the following with "date" in the select statement

SELECT name, date
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/15' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/15' THEN price2 END) AS price2_15
FROM table
GROUP BY name

then the output looks like this

A, 1.1, NULL, 3.2, NULL
A, NULL, 2.2, NULL, 4.4
B, 4.3, NULL, 3.5, NULL
B, NULL, 5.5, NULL, 6.6

is there a way for me to merge them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-10 : 02:50:27

but you want it one row per name isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timlisten
Starting Member

26 Posts

Posted - 2012-06-10 : 09:54:40

yes, that's why the above is still not right.

A, 1.1, NULL, 3.2, NULL
A, NULL, 2.2, NULL, 4.4
B, 4.3, NULL, 3.5, NULL
B, NULL, 5.5, NULL, 6.6

I want to merge the above output into the following since I couldn't use 1 select to get output.
A, 1.1, 2.2, 3.2, 4.4
B, 4.3, 5.5, 3.5, 6.6

Is there a way to do that?

timlisten
Starting Member

26 Posts

Posted - 2012-06-10 : 11:49:28

hey visakh16, I got it. I modified your script a little bit and it worked.
Just need to inner join them and filter out the null values.

Thanks for all the help, guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-10 : 14:41:22

glad that you sorted it out!
But in future please post some sample data to EXACTLY show you scenario and explain what you want
In this case unless you show us we could never imagine that problem was NULL values involved in between

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timlisten
Starting Member

26 Posts

Posted - 2012-06-10 : 23:22:27

Thanks visakh16, actually there is no null value in my data. The output I showed is the output I want. I mean the reason I have null values was because I
inner join the query.

SELECT name, date
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/15' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/15' THEN price2 END) AS price2_15
FROM table
GROUP BY name, date) X
INNER JOIN
(
SELECT name, date
MAX(CASE WHEN date = '7/14' THEN price1 END) AS price1_14,
MAX(CASE WHEN date = '7/14' THEN price2 END) AS price2_14,
MAX(CASE WHEN date = '7/15' THEN price1 END) AS price1_15,
MAX(CASE WHEN date = '7/15' THEN price2 END) AS price2_15
FROM table
GROUP BY name, date) Y

after the above inner join, it would produce NULL values, and I just filter out these and leave with the merged rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources