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
– Sid M, Commented Nov 21, 2016 at 7:56

DForck42 · Accepted Answer · 2016-11-21 14:31:25Z

2

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

edited Nov 21, 2016 at 14:31

answered Nov 18, 2016 at 22:05

DForck42

20.5k14 gold badges61 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sid M Over a year ago

your query returns duplicate records, is there any way to get unique records.

DForck42 Over a year ago

@SidM I fixed my typo. let me know if this works correctly now

Sid M Over a year ago

No, its still giving the duplicate results

DForck42 Over a year ago

@SidM ok, I think I've fixed it

Sid M Over a year ago

Thanks for the answer :)

SS_DBA · Accepted Answer · 2016-11-18 16:57:01Z

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, 2016 at 16:57

SS_DBA

2,4211 gold badge13 silver badges15 bronze badges

2 Comments

Sid M Over a year ago

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

SS_DBA Over a year ago

True. I was going based on your data.

DForck42 · Accepted Answer · 2016-11-18 21:54:52Z

-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, 2016 at 21:54

DForck42

20.5k14 gold badges61 silver badges86 bronze badges

answered Nov 18, 2016 at 16:37

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

5 Comments

Sid M Over a year ago

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

Kannan Kandasamy Over a year ago

Provide your input sample data as scripts

Sid M Over a year ago

shared the scripts

Kannan Kandasamy Over a year ago

Updated answer based on your input scripts. It is working fine for me..

Sid M Over a year ago

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.

Collectives™ on Stack Overflow

How to convert Rows to Columns in Sql

3 Answers 3

5 Comments

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related