0

This is my table:

schedid     Name     isPresent     Date
-------------------------------------------
1           James    Yes           9/2/2017
2           James    Yes           9/3/2017
3           James    Yes           9/4/2017
4           Rob      Yes           9/2/2017
5           Rob      Yes           9/4/2017

I want to display it like this:

Name      9/2/2017     9/3/2017     9/4/2017
--------------------------------------------
James     Yes          Yes          Yes
Rob       Yes                       Yes

What query should I use? Please help me..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
redbull
  • 11
  • 9
  • 1
    [This](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) might be what you're looking for. – dotNET Sep 27 '17 at 03:20
  • 1
    Google sql server pivot. There are a ton of examples for this case. – S3S Sep 27 '17 at 03:20

2 Answers2

0

The "manually coded" version would look similar to the following... If a dynamic version is needed, it is also pretty easy to do as well.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    schedid INT,
    Name VARCHAR(10),
    isPresent CHAR(3),
    [Date] date
    );
INSERT #TestData (schedid, Name, isPresent, Date) VALUES
    (1, 'James', 'Yes', '9/2/2017'),
    (2, 'James', 'Yes', '9/3/2017'),
    (3, 'James', 'Yes', '9/4/2017'),
    (4, 'Rob  ', 'Yes', '9/2/2017'),
    (5, 'Rob  ', 'Yes', '9/4/2017');

--==================================================

SELECT 
    td.Name,
    [2017-09-02] = MAX(CASE WHEN td.[Date] = '2017-09-02' THEN td.isPresent ELSE '' END),
    [2017-09-03] = MAX(CASE WHEN td.[Date] = '2017-09-03' THEN td.isPresent ELSE '' END),
    [2017-09-04] = MAX(CASE WHEN td.[Date] = '2017-09-04' THEN td.isPresent ELSE '' END)
FROM
    #TestData td
GROUP BY
    td.Name;

Results...

Name       2017-09-02 2017-09-03 2017-09-04
---------- ---------- ---------- ----------
James      Yes        Yes        Yes
Rob        Yes                   Yes
Jason A. Long
  • 4,382
  • 1
  • 12
  • 17
0

I've done it using Pivot functionality.

;With PivotData
As
(
    Select 
        Name, -- grouping element
        [Date], -- spreading element
        IsPresent -- aggregating element
    From MyTable
)
Select Name, [2017-09-02], [2017-09-03], [2017-09-04]
From PivotData
Pivot (Max(IsPresent) For [Date] In ([2017-09-02], [2017-09-03], [2017-09-04])) As P;

Hope this is helpful :)

Edit: I've made this query dynamic one. Try it out and let me know.

DECLARE @query VARCHAR(4000)
    ,@Dates VARCHAR(4000)

SET @Dates = STUFF((
            SELECT DISTINCT '],[' + Cast([Date] As Varchar)
            FROM MyTable
            ORDER BY '],[' + Cast([Date] As Varchar)
            FOR XML PATH('')
            ), 1, 2, '') + ']'

SET @query = '
;With PivotData
As
(
    Select 
        Name, -- grouping element
        [Date], -- spreading element
        IsPresent-- aggregating element
    From MyTable
)
Select *
From PivotData
Pivot (Max(IsPresent) For [Date] In (' + @Dates + ')) As P;'

EXEC (@query);
Maverick Sachin
  • 874
  • 7
  • 12