Show multiple rows data against entered ID in one row without PIVOT?

Question

I have attendance record against ID which show he/she was present on that day or not and I want to show the data through sql query in one single row which is in multiple rows against that ID. SO please help me figure out this problem asap.

Get this result from database after executing the query:

abc   1/2/2013      Present

abc   2/2/2013      Present

abc   3/2/2013      Present

abc   4/2/2013      Present

abc   5/2/2013      Present

abc   6/2/2013      Present

Expected Result:

Name   Date1     Date2     Date3      Date4       Date5

abc    Present   Present   Present    Present     Present

First, what RDBMS are you using? Second, why are disqualifying what might be the best option? Third, does it have to be with sql or is there application code such as .net, coldfusion, etc that is available? — Dan Bracuk, Apr 16 '13 at 16:33
(1) What is your RDBMS (SQL Flavor) Does it support something similar to `GROUP_CONCAT`? (2) By "*without PIVOT*" do you mean that you you just can't use `PIVOT` clause or any implementation of such functionality as well? — PM 77-1, Apr 16 '13 at 16:33
I'm using SQL but I want to show data of multiple rows in one row between the selected date range. — Malik, Apr 16 '13 at 16:36
Yes and I want to convert the multiple rows in one row through query. — Malik, Apr 16 '13 at 17:19
Hi, could you provide sample data and expected result? I believe you're looking how to use GROUP BY Statement — Allan Chua, Apr 16 '13 at 17:23
Suppose I get this result after getting data abc 1/2/2013 Present abc 2/2/2013 Present abc 3/2/2013 Present abc 4/2/2013 Present abc 5/2/2013 Present Expected Result: Name Date1 Date2 Date3 Date4 Date5 abc Pres Pres Pres Pres Pres — Malik, Apr 16 '13 at 17:25
Have a look at: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server Since SQL Server does not have direct analogue of MySql's `GROUP_CONCAT` you will need to use `FOR XML PATH` work-around (assuming your SQL Server is version **2005 or later**). — PM 77-1, Apr 16 '13 at 17:27

score 1 · Accepted Answer · answered Apr 16 '13 at 17:36

If you must not use PIVOT, you can get the same effect using row numbers and grouping (DEMO):

select
  name
  ,max(case when rn=1 then description end) as date1
  ,max(case when rn=2 then description end) as date2
  ,max(case when rn=3 then description end) as date3
  ,max(case when rn=4 then description end) as date4
  ,max(case when rn=5 then description end) as date5
from (
  select name, date, description,
    row_number() over (partition by name order by date) rn from Table1
) T
group by name

Note that this will only handle as many dates as you hard-code into the query. If it must be dynamic (i.e., you don't know how many date columns you need ahead of time), then you may need to dynamically-generate the SQL code above based on maximum date count per group.

Show multiple rows data against entered ID in one row without PIVOT?

1 Answers1