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 https://stackoverflow.com/questions/15745042/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)
Best Answer
You are not explaining why your PIVOT query did not work for you, although it is not too hard to guess if your
Response
sample is representative enough of the data in that table in general. You have two sets of responses for User 2 but your query picks up only one.As your expected output shows you want the query to return both sets, you need to teach it to distinguish between various sets of answers by the same user. One way is to use the ROW_NUMBER analytic function:
The query above makes a few assumptions:
Id
), or, at least, if oneFromDate
response comes later than anotherFromDate
response, then its relatedToDate
response also comes later than theToDate
response related to the previousFromDate
.If there are special cases in the real data (e.g. some sets may be incomplete), a different approach may be required.