March 28, 2010 at 11:15 pm
Dear all, please help me. I have a select query that currently produces the following results:
DoctorName Team 1 2 3 4 5 6 7 ... 31 Visited
dr. As A x x ... 2 times
dr. Sc A x ... 1 times
dr. Gh B x ... 1 times
dr. Nd C ... x 1 times
The problem is I want to produce the following result:
Name |Team| Date |HasVisited
dr.As | A | 1 Mar 2010|Yes
dr.As | A | 2 Mar 2010|No
|
|
|
|
dr.As | A | 23 Mar 2010|No
dr.As | A | 24 Mar 2010|No
|
|
|
|
dr.As | A | 31 Mar 2010|No
Can somebody help me unpivot this? I really appreciate this. I'm newbie in sql
March 29, 2010 at 1:06 am
Lynn petis, jeff moden, steve jones, gail shaw, garadin, jack, rbarry, paul, anirban, gsquared, ninja , nabha ,cirqudesqliel etc etc etc etc- these are all stalwarts here, and u can be rest assured ur query will be addressed..but they can help u if u post SAMPLE DATA , UR TABLE STRUCTURE , INPUT, EXPECTED OUTPUT etc etc because they have lot of other things to do..
Please read thro the following article from Jeff on posting etiquettes
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Please help us help u!!
As far ur query is concerned, i have created sample table, data and the result set u needed..
Sample table and Data:
SET NOCOUNT ON
DECLARE @CreateQuery VARCHAR(MAX)
DECLARE @daycolumns VARCHAR(MAX)
DECLARE @count int
SET @CreateQuery = '
IF OBJECT_ID(''DoctorVisits'') IS NOT NULL
DROP TABLE DoctorVisits
CREATE TABLE DoctorVisits
(
ID INT IDENTITY(1,1) NOT NULL,
DoctorName VARCHAR(64) NOT NULL,
Team VARCHAR(64) NOT NULL,
'
SET @daycolumns = ''
SET @count = 1
WHILE @count <=31
BEGIN
SET @daycolumns = @daycolumns + 'Day'+CAST(@count AS CHAR(2))+ ' INT,'
SET @count = @count + 1
END
SET @CreateQuery = @CreateQuery + @daycolumns + ' Num_Of_Visits INT )'
--SELECT @CreateQuery
EXEC (@CreateQuery)
INSERT INTO DoctorVisits
SELECT 'Dr.A' , 'A',1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0, 20
UNION ALL
SELECT 'Dr.B' , 'B',1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0, 20
UNION ALL
SELECT 'Dr.AA' , 'A',1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0, 20
UNION ALL
SELECT 'Dr.BB' , 'B',1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0, 20
--SELECT * from DoctorVisits
And here goes ur query
SELECT
DoctorName
,Team
,DayOfTheMonth
,CASE Num_Of_Visits
WHEN 1 THEN 'Yes'
ELSE 'NO'
END AS HasVisited
FROM
(SELECT doctorname, team ,
Day1,Day2,Day3,Day4,Day5,Day6,Day7,Day8,Day9,Day10,
Day11,Day12,Day13,Day14,Day15,Day16,Day17,Day18,Day19,Day20,
Day21,Day22,Day23,Day24,Day25,Day26,Day27,Day28,Day29,Day30,Day31
FROM doctorvisits ) innerpivot
UNPIVOT
(Num_Of_Visits FOR DayOfTheMonth IN
(Day1,Day2,Day3,Day4,Day5,Day6,Day7,Day8,Day9,Day10,
Day11,Day12,Day13,Day14,Day15,Day16,Day17,Day18,Day19,Day20,
Day21,Day22,Day23,Day24,Day25,Day26,Day27,Day28,Day29,Day30,Day31)
) AS UnPivotTable
ORDER BY doctorname, team
Please inform us if this is what u need..
Cheers,
C'est Pras!:-)
March 29, 2010 at 1:26 am
Dear ColdCofee,
Thank you so much for your help, I think your answer is the solution that I've looking for..
about the following article from Jeff on posting etiquettes that you send. I 'v so thankful. I'm newbie in this forum so I don't know the etiquette beside my english is bad. Sometimes I got problem to translate my question/problem in english so people can understand my meaning. I just put the keyword so people will not misleading me
Thank you so much for your answer and you link for etiquette so next time I will ask a better question.
March 29, 2010 at 2:04 am
Chandradyani (3/29/2010)
Dear ColdCofee,Thank you so much for your answer and you link for etiquette so next time I will ask a better question.
Welcome Mate... and thanks for going thro the etiquette article , hope to see your subsequent posts make others' efforts easier 🙂
And for the english thing, i guess even shakespeare nor thiruvalluvar would have been 200% in their respective languages :w00t:, so no issues 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply