SQL Pivot select multiple rows

Question

This is the table I have:

I want to create pivot like this:

I've wrote some query, but I get only names diagonally:

(
select 
[1] as Client0, 
[2] as Client1, 
[3] as Client2, 
[4] as Client3,
[5] as Client4
from 
(
select 
    rc.DateCreated, 
    gd.Name,
    DENSE_RANK() over (order by gd.ID_TableGD) as colnum
from TableGD gd
inner join TableRC rc ON gd.ID_TableGD = rc.ID_TableRC
WHERE gd.ID_TableGD IN (962,1029,1024) 
AND gd.Active = 1
) as t
pivot
(
MAX(Name)
for colnum in 
(
[1],
[2],
[3],
[4],
[5]

)
) as pvt) 

So, I want to be able for particular client to get clientName (ClientA) and CreatedDate in same column.

This is my first pivot, and I am not sure if I need to put Max(Name)?

And I get table like this:

Answer 1

I would use CROSS APPLY with Value to do that.

first, you need to do unpivot to let your DateCreated and Name in one column, which is from your expected result.

let result look like this.

SELECT 
  v.*
FROM T t1 CROSS APPLY(
    VALUES 
    (Name,colnum,1),
    (CAST(DateCreated AS VARCHAR(50)),colnum,2)
) v (val,colnum,grp)

Results:

|        val | colnum | grp |
|------------|--------|-----|
|    ClientA |      1 |   1 |
| 2018-08-21 |      1 |   2 |
|    ClientB |      2 |   1 |
| 2018-08-22 |      2 |   2 |
|    ClientC |      3 |   1 |
| 2018-08-23 |      3 |   2 |

---

Next Step you can try to use Pivot let vertical data to horizontal.

CREATE TABLE T(
   Name VARCHAR(50),
   DateCreated DATE,
   colnum INT
);
INSERT INTO T VALUES('ClientA','2018-08-21',1);
INSERT INTO T VALUES('ClientB','2018-08-22',2);
INSERT INTO T VALUES('ClientC','2018-08-23',3);

Query 1:

SELECT 
    MAX(CASE WHEN v.colnum = 1 THEN v.val end) client0,
    MAX(CASE WHEN v.colnum = 2 THEN v.val end) client1,
    MAX(CASE WHEN v.colnum = 3 THEN v.val end) client2,
    MAX(CASE WHEN v.colnum = 4 THEN v.val end) client3,
    MAX(CASE WHEN v.colnum = 5 THEN v.val end) client4
FROM T t1 CROSS APPLY(
    VALUES 
    (Name,colnum,1),
    (CAST(DateCreated AS VARCHAR(50)),colnum,2)
) v (val,colnum,grp)
group by grp

Results:

|    client0 |    client1 |    client2 | client3 | client4 |
|------------|------------|------------|---------|---------|
|    ClientA |    ClientB |    ClientC |  (null) |  (null) |
| 2018-08-21 | 2018-08-22 | 2018-08-23 |  (null) |  (null) |

CROSS APPLY ... Value

NOTE

If you want to show your original datetime format.

You could use CONVERT or FORMAT to translate your expect datetime format.

This sample is for FORMAT http://sqlfiddle.com/#!18/12ae9e/3

Although format is simpler to use than Convert, it is poor performance when it comes to multiple data.

Answer 2

Use max in time of selection like below:

select 
max([1]) as Client0, 
max([2]) as Client1, 
max([3]) as Client2, 
max([4]) as Client3,
max([5]) as Client4
from 
(
select 
    rc.DateCreated, 
    gd.Name,
    DENSE_RANK() over (order by gd.ID_TableGD) as colnum
from TableGD gd
inner join TableRC rc ON gd.ID_TableGD = rc.ID_TableRC
WHERE gd.ID_TableGD IN (962,1029,1024) 
AND gd.Active = 1
) as t
pivot
(
MAX(Name)
for colnum in 
(
[1],
[2],
[3],
[4],
[5]

)
) as pvt