| Author |
 Topic  |
|
|
timlisten
Starting Member
USA
26 Posts |
 Posted - 06/08/2012 : 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
India
48064 Posts |
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 06/09/2012 : 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
India
48064 Posts |
Posted - 06/09/2012 : 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
USA
26 Posts |
Posted - 06/09/2012 : 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
India
48064 Posts |
Posted - 06/09/2012 : 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
USA
26 Posts |
Posted - 06/10/2012 : 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
India
48064 Posts |
Posted - 06/10/2012 : 01:36:30
|
nope they wont. show your full query
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/10/2012 : 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? |
Edited by - timlisten on 06/10/2012 02:12:30 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 06/10/2012 : 02:50:27
|
but you want it one row per name isnt it?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
timlisten
Starting Member
USA
26 Posts |
Posted - 06/10/2012 : 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
USA
26 Posts |
Posted - 06/10/2012 : 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. |
Edited by - timlisten on 06/10/2012 11:50:28 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 06/10/2012 : 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
USA
26 Posts |
Posted - 06/10/2012 : 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.
|
Edited by - timlisten on 06/10/2012 23:23:00 |
|
|
| |
Topic |
|
|
|
How to merge rows into colum
