2

I've a table Columns

enter image description here

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

enter image description here

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

enter image description here

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)
6
  • first of all - You must fix main query - 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 it is wrong - it has different number of columns, You need add criteria for convert TEXT to projects and comments, possible using CASE, and then start transform result table
    – a_vlad
    Nov 21 '16 at 8:30
  • @a_vlad : ok, could you please show me how that can be done?
    – Sid M
    Nov 21 '16 at 8:35
  • I don't know - how there start chat, because code in comment look bad, but it not full answer so not ready public it as final answer select R.UserId AS ID, CASE WHEN r.ColumnId = 1 THEN R.Text END AS FromDate, CASE WHEN r.ColumnId = 2 THEN R.Text END AS ToDate, CASE WHEN r.ColumnId = 3 THEN R.Text END AS Project, CASE WHEN r.ColumnId = 4 THEN R.Text END AS Comment from [Columns] C INNER JOIN Response R ON C.Id=R.ColumnId
    – a_vlad
    Nov 21 '16 at 8:47
  • all simple if have 1 more column - sequence number of project for selected user, in other case need to do how to split user #2 for 2 separate
    – a_vlad
    Nov 21 '16 at 9:01
  • @a_vlad: what you said in query, I already tried that but it didn't work.
    – Sid M
    Nov 21 '16 at 9:41
5

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:

SELECT
  UserId,
  FromDate,
  ToDate,
  Project,
  Comment
FROM
  (
    SELECT
      r.UserId,
      r.Text,
      c.ColumnName,
      SetNo = ROW_NUMBER() OVER (PARTITION BY r.UserId, r.ColumnId ORDER BY r.Id)
    FROM
      dbo.Columns AS c
      INNER JOIN dbo.Response AS r ON c.Id = r.ColumnId
  ) AS derived
  PIVOT
  (
    MAX(Text)
    FOR ColumnName IN (FromDate, ToDate, Project, Comment)
  ) AS p
;

The query above makes a few assumptions:

  • every set of answers consists of the same set of ColumnIds;
  • related responses are arranged together (based on the order of Id), or, at least, if one FromDate response comes later than another FromDate response, then its related ToDate response also comes later than the ToDate response related to the previous FromDate.

If there are special cases in the real data (e.g. some sets may be incomplete), a different approach may be required.

0
-2

I've done something similar in MySQL but I'm sure you could make some changes to make it work for your need. Take a look at http://knowittoall.blogspot.com/2011/09/converting-data-attribute-to-table.html and http://knowittoall.blogspot.com/2011/09/converting-data-attribute-to-table_18.html

1
  • Please try to add some details to the answer, it will help more.
    – Marian
    Nov 24 '16 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.