代码之家 › 专栏 › 技术社区 › ctrlShiftBryan

不带聚合函数的TSQL Pivot

pivot-without-aggregate pivot tsql sql-server sql

106

ctrlShiftBryan · 技术社区 · 17 年前

我有一张这样的桌子。。。

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            Date           12/1/2009

我想要这个。。。

使用PIVOT是否可以实现这一点?

CustomerID  FirstName   MiddleName          LastName        Date
----------------------------------------------------------------------
1           Joe             S               Smith           12/12/2009
2           Sam             S               Freddrick       1/12/2009
3           Jaime           S               Carol           12/1/2009

9 回复 | 直到 14 年前

188

ctrlShiftBryan 15 年前

是的,但是为什么!!??

   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

118

gbn 17 年前

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

在这种情况下,您还可以在customerid上进行5次自联接,按每个表引用的dbColumnName进行筛选。这可能会更好。

Vishwanath Dalvi 12 年前

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;

ctrlShiftBryan 17 年前

好的,很抱歉这个糟糕的问题。gbn让我走上了正确的轨道。这就是我想要的答案。

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

然后我不得不使用while语句,将上面的语句构建为varchar并使用dynmaicsql。

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并从表中选择不同的列名。谢谢你的回答。

shahkalpesh 17 年前

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。我希望你明白了。

Community Mohan Dere 9 年前

OP实际上不需要无表情地旋转,但对于来到这里了解如何观看的人来说:

sql parameterised cte query

这个问题的答案涉及到一种情况,即需要无聚合的pivot,因此解决方案的一部分就是一个这样做的示例。

mitchimus 12 年前

试试这个:

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

Pang Ajmal PraveeN 10 年前

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

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

Neo 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')