没有聚合函数的 TSQL Pivot

Question

我有一个这样的表...

CustomerID	DBColumnName	Data
1	FirstName	Joe
1	MiddleName	S
1	LastName	Smith
1	Date	12/12/2009
2	FirstName	Sam
2	MiddleName	S
2	LastName	Freddrick
2	Date	1/12/2009
3	FirstName	Jaime
3	MiddleName	S
3	LastName	Carol
3	日期	12/1/2009

我想要这个...

使用 PIVOT 可以吗？

客户	ID 名字	中间	名姓氏	日期
1	Joe	S	Smith	12/12/2009
2	Sam	S	Freddrick	1/12/2009
3	Jaime	S	Carol	12/1/2009

Answer 1

是的，但是为什么！！！

   Select CustomerID,
     Min(Case DBColumnName When 'FirstName' Then Data End) FirstName,
     Min(Case DBColumnName When 'MiddleName' Then Data End) MiddleName,
     Min(Case DBColumnName When 'LastName' Then Data End) LastName,
     Min(Case DBColumnName When 'Date' Then Data End) Date
   From table
   Group By CustomerId

Answer 2

您可以使用 MAX 聚合，它仍然可以工作。一个值的最大值 = 该值。

在这种情况下，您还可以在 customerid 上自连接 5 次，按每个表引用的 dbColumnName 进行过滤。效果可能会更好。

Answer 3

WITH pivot_data AS
(
SELECT customerid, -- Grouping Column
dbcolumnname, -- Spreading Column
data -- Aggregate Column
FROM pivot2 
)
SELECT customerid, [firstname], [middlename], [lastname]
FROM pivot_data
PIVOT (max(data) FOR dbcolumnname IN ([firstname],[middlename],[lastname])) AS p;

Answer 4

好吧，抱歉这个问题不好。 gbn 让我走上了正轨。
这就是我在答案中寻找的内容。

SELECT [FirstName], [MiddleName], [LastName], [Date] 
FROM #temp 
PIVOT
(   MIN([Data]) 
    FOR [DBColumnName] IN ([FirstName], [MiddleName], [LastName], [Date]) 
)AS p

然后我必须使用 while 语句并将上述语句构建为 varchar 并使用 dynmaic sql。

使用类似的东西

SET @fullsql = @fullsql + 'SELECT ' + REPLACE(REPLACE(@fulltext,'(',''),')','')
SET @fullsql = @fullsql + 'FROM #temp '
SET @fullsql = @fullsql + 'PIVOT'
SET @fullsql = @fullsql + '('
SET @fullsql = @fullsql + ' MIN([Data])'
SET @fullsql = @fullsql + ' FOR [DBColumnName] IN '+@fulltext
SET @fullsql = @fullsql + ')'
SET @fullsql = @fullsql + 'AS p'

EXEC (@fullsql)

使用 while 循环构建 @fulltext 并从表中选择不同的列名称。感谢您的回答。

Answer 5

OP实际上不需要在没有聚合的情况下进行旋转，但是对于那些来这里了解如何查看的人来说：

sql 参数化 cte 查询

该问题的答案涉及需要不进行聚合的数据透视的情况，因此执行此操作的示例是解决方案的一部分。

Answer 6

SELECT
main.CustomerID,
f.Data AS FirstName,
m.Data AS MiddleName,
l.Data AS LastName,
d.Data AS Date
FROM table main
INNER JOIN table f on f.CustomerID = main.CustomerID
INNER JOIN table m on m.CustomerID = main.CustomerID
INNER JOIN table l on l.CustomerID = main.CustomerID
INNER JOIN table d on d.CustomerID = main.CustomerID
WHERE f.DBColumnName = 'FirstName' 
AND m.DBColumnName = 'MiddleName' 
AND l.DBColumnName = 'LastName' 
AND d.DBColumnName = 'Date' 

编辑：我在没有编辑的情况下写了这篇文章还没有运行SQL。我希望，你明白了。

Answer 7

这应该有效：

select * from (select [CustomerID]  ,[Demographic] ,[Data]
from [dbo].[pivot]
) as Ter

pivot (max(Data) for  Demographic in (FirstName, MiddleName, LastName, [Date]))as bro

Answer 8

试试这个：

SELECT CUSTOMER_ID, MAX(FIRSTNAME) AS FIRSTNAME, MAX(LASTNAME) AS LASTNAME ...

FROM
(

SELECT CUSTOMER_ID, 
       CASE WHEN DBCOLUMNNAME='FirstName' then DATA ELSE NULL END AS FIRSTNAME,
       CASE WHEN DBCOLUMNNAME='LastName' then DATA ELSE NULL END AS LASTNAME,
        ... and so on ...
GROUP BY CUSTOMER_ID

) TEMP

GROUP BY CUSTOMER_ID

Answer 9

这是为数据透视查询构建动态字段的好方法：

--将值汇总到临时表中

declare @STR varchar(1000)
SELECT  @STr =  COALESCE(@STr +', ', '') 
+ QUOTENAME(DateRange) 
from (select distinct DateRange, ID from ##pivot)d order by ID

---查看生成的字段

print @STr

exec('  .... pivot code ...
pivot (avg(SalesAmt) for DateRange IN (' + @Str +')) AS P
order by Decile')

Answer 10

根据定义，所有数据透视都是聚合的，但是有一种简单的方法可以确保所有数据都得到透视。
除了枢轴之外的列是分组依据的列。因此，您可以在由其他分组依据划分的数据中创建一个 row_number 并将其包含在您的数据透视数据中。例如：

with data as (
    select 'a' key1,'1' key2,'samplecolumn' as col,'3' as val
    union all
    select 'a' key1,'1' key2,'samplecolumn' as col,'6' as val
    union all
    select 'a' key1,'2' key2,'samplecolumn' as col,'7' as val
    union all
    select 'a' key1,'2' key2,'samplecolumn' as col,'9' as val
    union all
    select 'b' key1,'1' key2,'samplecolumn' as col,'23' as val
    union all
    select 'b' key1,'1' key2,'samplecolumn' as col,'45' as val
),
data_with_rownum as (
    select *, ROW_NUMBER() over (partition by key1,key2 order by (select null)) rownum from data
)
select key1,key2,rownum,samplecolumn from data_with_rownum
PIVOT (max(val) FOR col IN (samplecolumn)) AS p;