집계 함수가없는 TSQL 피벗

집계 함수가없는 TSQL 피벗

---

나는 이런 식으로 테이블을 가지고있다 ...

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

---

MAX 집계를 사용할 수 있지만 여전히 작동합니다. 하나의 값의 MAX = 해당 값 ..

이 경우 customerid에서 5 번 자체 조인하고 테이블 참조 당 dbColumnName으로 필터링 할 수도 있습니다. 더 잘 작동 할 수 있습니다.

---

예, 그러나 왜 !! ??

   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

---

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;

---

좋아요, 불쌍한 질문에 대해 죄송합니다. 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를 작성하고 테이블에서 고유 한 열 이름을 선택하십시오. 답변 주셔서 감사합니다.

---

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을 실행하지 않았습니다. 나는 당신이 아이디어를 얻길 바랍니다.

---

The OP didn't actually need to pivot without agregation but for those of you coming here to know how see:

sql parameterised cte query

The answer to that question involves a situation where pivot without aggregation is needed so an example of doing it is part of the solution.

---

Try this:

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

---

This should work:

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

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

---

Here is a great way to build dynamic fields for a pivot query:

--summarize values to a tmp table

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

---see the fields generated

print @STr

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

참고URL : https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function