SQL查询的关系显示使用透视动态列 [英] Sql query relations display dynamic columns using pivot

问题描述

我有一个表的结​​构如下图所示：

I have a table structure like below:

表名称： questionsTable 并日期是看起来像

Table name: questionsTable and the date is look like

 qid      qName
  1        Enter your licence number.
  2        What is your favaorite sport.
  3        Enter your attendee name

另一个表名称： tbl_Answer 和数据看起来像

qid    attendeeid    Answer
 1       2349         45645645
 2       2349         Cricket
 3       2350         James
 2       2350         Chess
 1       2350         858585

现在我要显示我的输出看起来是这样的：

Now I want to display my output to look like this:

attendeeid   questionlable            answer     questionlable                 answer    questionlable          answer     
  2349        Enteryourlicencenumber  45645645   Whatisyourfavaoritesport      Cricket
  2350        Enteryourlicencenumber  858585     What is your favaorite sport  hockey   Enteryourattendeename  James

在这里，我想显示的问题拉布勒动态的，因为在这里我的样品已经采取3 QID。

Here I want to display question lable dynamic because here sample I have taken 3 qid.

推荐答案

不正是你要的，但它可能是一个更好的解决方案：

Not exactly what you asked for, but it possibly a better solution :

select attendeeid, [1] as [Enteryourlicencenumber], [2] as [Whatisyourfavaoritesport], [3] as [Enteryourattendeename]
from
    (select * from tbl_Answer) as p
pivot
    (min(Answer) for qid in ([1], [2], [3])) as pvt

这会导致：

attendeeid  Enteryourlicencenumber  Whatisyourfavaoritesport  Enteryourattendeename
----------  ----------------------  ------------------------  ---------------------
2349        45645645                Cricket                   NULL
2350        858585                  Chess                     James

设置：

create table questionsTable
(
    qid int primary key
    , qName varchar(max) not null
)

create table tbl_Answer
(
    qid int not null
    , attendeeid int not null
    , Answer nvarchar(max) not null
)

insert into questionsTable
select 1, 'Enter your licence number.'
union all
select 2, 'What is your favaorite sport.'
union all
select 3, 'Enter your attendee name'


insert into tbl_Answer
select 1, 2349, '45645645'
union all
select 2, 2349, 'Cricket'
union all
select 3, 2350, 'James'
union all
select 2, 2350, 'Chess'
union all
select 1, 2350, '858585'

这篇关于SQL查询的关系显示使用透视动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！