My SQL skills are a bit rusty, and I need some help with a complex query. Here's an example of the table data:
Date Column2 Column3 Column4
2016-03-18 A 1000 5.43
2016-03-18 B 1000 2.01
2016-03-18 B 1250 3.53
2016-03-18 B 1500 1.56
2016-03-18 A 1500 8.24
2016-03-18 A 1750 2.19
2016-03-19 A 1000 4.21
2016-03-19 B 1000 1.98
2016-03-19 B 1250 3.48
2016-03-19 B 1500 1.79
2016-03-19 A 1500 8.50
I need the SQL query to combine rows that have the same value in Column3 for a given date (first column). In the source table there should be one row with "A" in Column2 and one row with "B" in Column2 for every instance of the date and the value in Column3. If there isn't a corresponding row for either "A" or "B" in the source table (I included a few examples above), I would like the AValue or BValue in the result set to be null if there isn't a row. Here's what the result set should look like:
Date Column3 AValue BValue
2016-03-18 1000 5.43 2.01
2016-03-18 1250 null 3.53
2016-03-18 1500 8.24 1.56
2016-03-18 1750 2.19 null
2016-03-19 1000 4.21 1.98
2016-03-19 1250 null 3.48
2016-03-19 1500 8.50 1.79
I'm using MS SQL Server 2012, so the resulting query should be compatible with that database engine. I would prefer not to use a stored procedure or any code, hopefully this can all be done in straight SQL by someone much more knowledgeable than me. :-)
Hi there,
I would love to help you with your SQL. Here's my proposed solution using a full outer join.
select
case when [login to view URL] is null then [login to view URL] else [login to view URL] end as Dt
,case when [login to view URL] is null then [login to view URL] else [login to view URL] end as Col2
,[login to view URL] as ValueA
,[login to view URL] as ValueB
from
(select * from ##test where Col1 = 'A') as a
full outer join
(select * from ##test where Col1 = 'B') as b
on [login to view URL] = [login to view URL]
and [login to view URL] = [login to view URL]
Hope this helps.
Marcelo
$12 USD in 0 day
5.0 (3 reviews)
2.0
2.0
8 freelancers are bidding on average $24 USD for this job
Hello,
I have profissional experience of SQL. Could you please provide me the name of the table and the columns in order to construct the query with the correct fields.
Thank you,
Best regards,
David
I have knowledge and experience of writing complex SQL queries as part of my Job. I have done data warehousing testing which mainly revolves around complex SQL queries.