How to convert Rows to Columns in Sql

Question

I've a table Columns

and a second table Response in which all data is saved.

Now I want to create a SQL View in which the result should be like this

I tried using pivot

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.Text , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  max(Text)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;

but that didn't worked for me, I also referred this Efficiently convert rows to columns in sql server but was not able to implement it. Any ideas how to achieve the same in SQL View?

Scripts for Tables:

CREATE TABLE [dbo].[Columns](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](1000) NULL,
    [IsActive] [bit] NULL,
 CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into [Columns] values('FromDate',1)
insert into [Columns] values('ToDate',1)
insert into [Columns] values('Project',1)
insert into [Columns] values('Comment',1)

CREATE TABLE [dbo].[Response](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint]  NOT NULL,
    [ColumnId] [bigint]  NOT NULL,
    [Text] [nvarchar](max) NULL,
    [IsActive] [bit] NULL,
    CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into [Response] values(1,1,'1/1/2012',1)
insert into [Response] values(1,2,'1/2/2012',1)
insert into [Response] values(1,3,'p1',1)
insert into [Response] values(1,4,'c1',1)
insert into [Response] values(2,1,'1/1/2013',1)
insert into [Response] values(2,2,'1/2/2013',1)
insert into [Response] values(2,3,'p2',1)
insert into [Response] values(2,4,'c2',1)
insert into [Response] values(2,1,'1/1/2014',1)
insert into [Response] values(2,2,'1/2/2014',1)
insert into [Response] values(2,3,'p3',1)
insert into [Response] values(2,4,'c3',1)
insert into [Response] values(3,1,'1/1/2015',1)
insert into [Response] values(3,2,'1/2/2015',1)
insert into [Response] values(3,3,'p4',1)
insert into [Response] values(3,4,'c4',1)

@DForck42 : Like I mentioned the desired output in my question, each record for the corresponding `UserId` should come without duplicate records. — Sid M, Nov 21 '16 at 07:56

DForck42 · Accepted Answer · 2016-11-21T14:31:25.060

Honestly, if the column types aren't going to change, or you only need a subset of them, you could just filter them out and then join on them rather than write a pivot. I wrote it using a cte, but they could just as easily be sub-queries:

;with fd as
(
    select
        UserID,
        [Text] as FromDate,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 1
),
td as
(
    select
        UserID,
        [Text] as ToDate,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 2
),
p as
(
    select
        UserID,
        [Text] as Project,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 3
),
c as
(
    select
        UserID,
        [Text] as Comment,
        row_number() over (partition by userID order by ID) as DEDUP
    from response
    where ColumnID = 4
)
select
    fd.*,
    td.ToDate,
    p.Project,
    c.Comment
from fd
    inner join td
        on fd.UserId = td.UserId
            and fd.DEDUP = td.DEDUP
    inner join p
        on fd.UserId = p.UserId
            and fd.DEDUP = p.DEDUP
    inner join c
        on fd.UserId = c.UserId
            and fd.DEDUP = c.DEDUP

your query returns duplicate records, is there any way to get unique records. — Sid M, Nov 21 '16 at 08:14
@SidM I fixed my typo. let me know if this works correctly now — DForck42, Nov 21 '16 at 14:14

score 0 · Answer 2 · answered Nov 18 '16 at 16:57

0

Try this. I worked on your answer.

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.RText , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  Min(Rtext)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv

UNION
select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.RText , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  Max(Rtext)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;

answered Nov 18 '16 at 16:57

SS_DBA

2,403
1
11
15

`max` and `min` functions returns null for date type values, here for columns `FromDate` and `Todate`. Also you are considering only two rows for the UserId=2, there can be more rows for any UserId – Sid M Nov 18 '16 at 17:01
True. I was going based on your data. – SS_DBA Nov 18 '16 at 17:58

score -1 · Answer 3 · edited Nov 18 '16 at 21:54

-1

You can query like this

;with cte as 
(
    select r.*, 
    c.name 
    from Response r 
        inner join Columns c
            on r.columnid = c.id
) 
select 
    Userid, 
    max([FromDate]) as [FromDate],
    max([ToDate]) as [ToDate],
    max([Project]) as [Project],
    max([Comment]) as [Comment] 
from cte
pivot
(
    max(Text) for name in ([FromDate], [ToDate], [Project], [Comment])
) p
group by userid

edited Nov 18 '16 at 21:54

DForck42

19,789
13
59
84

answered Nov 18 '16 at 16:37

Kannan Kandasamy

13,405
3
25
38

2 problems in the answer, `1` : `max` function returns null for `date` type values, here for columns `FromDate` and `Todate`. `2` : only 1 row returned for `UserId=2` – Sid M Nov 18 '16 at 16:51
Provide your input sample data as scripts – Kannan Kandasamy Nov 18 '16 at 17:04
shared the scripts – Sid M Nov 18 '16 at 17:14
Updated answer based on your input scripts. It is working fine for me.. – Kannan Kandasamy Nov 18 '16 at 19:05
I don't get the result for project `p2`, did you really run the query you mentioned in your answer or just edited and posted it here. P.S. I didn't downvote your answer. – Sid M Nov 21 '16 at 07:53

How to convert Rows to Columns in Sql

3 Answers3