在这种情况下，如何将行加入动态列? [英] How do i join the rows into dynamic columns in this case?

问题描述

我很抱歉发布这个，我的感觉，几乎是重复的.但我已经尝试了我找到的解决方案，但没有让它在我的解决方案中工作:(

I'm sorry to post this, what i feel, almost duplicate. But i've tried the solutions i've found but haven't gotten it to work in my solution :(

这是在我 FUBAR 之前 SQL 的样子.这以非常好的格式返回数据.但是我得到了具有相同数据的重复行，只是问题和答案发生了变化.我希望问题是列名和它的值的答案.

This is how the SQL looks before i FUBARed it. This returns the data in a pretty good format. But i get duplicate rows with the same data except that the questions and answers are changed. I'd like that the Question would be the column name and the answer it's value.

SELECT c.*, sa.Question, sa.Answer
    FROM Customers as c, Surveys s, SurveyAnswers sa 
    WHERE c.OrderID IN(SELECT id FROM @orders)
        AND s.CustomerID = c.id
        AND sa.SurveyID = s.ID

我的 SQL 很弱，我必须尽快完成 :( 另一种方法是在 .net 应用程序中进行更繁重的工作，但我很乐意直接获取数据此致，迈克尔

My SQL is weak and i got to get this done asap :( The alternative is to do the more heavy lifting in the .net app but i'd be nice to get the data directly Best regards, Mikael

推荐答案

您想使用 Bluefeet's answer here 来完成动态列数据透视.(为简洁起见，我省略了您的 @orders 过滤器):

You want to use Bluefeet's answer here to accomplish a dynamic column pivot. (I've omitted your @orders filter for brevity):

DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(sa.Question) 
            FROM SurveyAnswers sa
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CustomerID, Name, ' + @cols + ' from 
            (
                select 
                    c.ID as CustomerId, 
                    c.Name,
                    sa.Question, 
                    sa.Answer
                FROM Customers as c
                     INNER JOIN Surveys s ON s.CustomerID = c.id
                     INNER JOIN SurveyAnswers sa ON sa.SurveyID = s.ID
           ) x
            pivot 
            (
                min(Answer)
                for Question in (' + @cols + ')
            ) p '
 execute(@query);

这里的SqlFiddle

这篇关于在这种情况下，如何将行加入动态列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！