0

I want to display rows in different column

Current situation 

CASEID  DATE OF SERVICE
760      12/30/2008
760      12/30/2008
817      12/30/2008
817      12/30/2008
817      12/01/2009

Now I want to display it as 

CASEID [DATE OF SERVICE1] [DATE OF SERVICE2] [Date of Service3] so-on
760      12/30/2008        12/30/2008
817       12/30/2008       12/30/2008        12/01/2009
Razim Khan
  • 337
  • 5
  • 23
  • 1
    Looks to be a dynamic pivot on a row number. use `row_number() over partition by caseID` to give you a column that can then be used to pivot, and dynamic SQL `pivot`. https://msdn.microsoft.com/en-us/library/ms186734.aspx for row_num to get unique columns, and http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query gives you a dynamic pivot – xQbert Sep 02 '15 at 21:05
  • 1
    possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – jpw Sep 02 '15 at 21:08

2 Answers2

1

You can use a CTE to generate row numbers, then self join odd and even rows and select from there:

WITH rn AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY CASEID ORDER BY [DATE OF SERVICE]) rn
  FROM Cases
)
SELECT r1.CASEID, r1.[DATE OF SERVICE] as [DATE OF SERVICE1],
       r2.[DATE OF SERVICE] as [DATE OF SERVICE2]
FROM rn r1 LEFT JOIN rn r2
  ON r1.rn = r2.rn - 1 AND r1.CASEID = r2.CASEID
WHERE (r1.rn % 2) = 1

Also on SQLFiddle.

Amit
  • 45,440
  • 9
  • 78
  • 110
  • this is working quite nice for , but if we have [date of service] more than 2 , for some ids then how to handle that? Thanks in advance – Razim Khan Sep 02 '15 at 21:35
  • I thought you wanted 2 columns. Make yourself clear, edit the question. – Amit Sep 02 '15 at 21:38
0

You can use Pivot if you know the maximum number of dates for any CaseID. (Here, I assumed that I have 5 dates at max for any CaseID).

Select CaseID, [1] As [DOS1], [2] As [DOS2], [3] As [DOS3], [4] As [DOS4],   
[5] As [DOS5]
From
(Select CaseID, [Date Of Service], 
ROW_NUMBER() Over(Partition By CaseID Order by [Date Of Service]) RowNum
From Cases ) P
Pivot 
(
Max([Date Of Service]) For RowNum in ([1], [2], [3], [4], [5]) 
) As Pvt 
Order by CaseID
Sravan Kumar
  • 51
  • 4