December 12, 2011 at 9:23 am
I am trying to place the first and second values on the same row. Here is an example of what I want.
WHAT I HAVE:
Cust-----Doctors
A ---------1
A ---------2
A ---------3
B ---------1
B ---------2
B ---------3
WHAT I WANT:
Cust----Doctor_1------Doctor_2
A ---------1---------------2
B ---------1---------------2
HELP!!!
December 12, 2011 at 9:47 am
BEGIN TRAN
--First, create the sample data to play with
SELECT Cust, Doctors
INTO yourSampleData
FROM (SELECT 'A', 1
UNION ALL SELECT 'A', 2
UNION ALL SELECT 'A', 3
UNION ALL SELECT 'B', 1
UNION ALL SELECT 'B', 2
UNION ALL SELECT 'B', 3) a(Cust, Doctors)
--Next, look at the query required
SELECT Cust,
MAX(CASE WHEN Doctors = 1 THEN Doctors END) AS Doctor_1,
MAX(CASE WHEN Doctors = 2 THEN Doctors END) AS Doctor_2,
MAX(CASE WHEN Doctors = 3 THEN Doctors END) AS Doctor_3
FROM yourSampleData
GROUP BY Cust
--OK, so we know what we want to produce, lets make it dynamic
--so we don't always have to know how many "doctor"'s there are
DECLARE @sql VARCHAR(MAX)
--First, build up the CASE statement part
SELECT @sql = COALESCE(@SQL,'') +
',MAX(CASE WHEN Doctors = '+CONVERT(VARCHAR(5),Doctors)+' THEN Doctors END) AS Doctor_'+CONVERT(VARCHAR(5),Doctors)
FROM yourSampleData
GROUP BY Doctors
--Next, add the SELECT and FROM
SET @sql = 'SELECT Cust' + @sql + ' FROM yourSampleData GROUP BY Cust'
--Finally, execute the statement
EXEC(@SQL)
ROLLBACK
December 12, 2011 at 9:48 am
I am not great in writing scripts super fast.
From the look of it, you should try using Pivot, that should get you there..
Look at this link to read see an example.. http://stackoverflow.com/questions/24470/sql-server-pivot-examples
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply