TSQL Pivot bez funkcji agregującej


139

Mam taki stół ...

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

Chcę tego ...

Czy jest to możliwe przy użyciu 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

Odpowiedzi:


102

Możesz użyć agregatu MAX, to nadal będzie działać. MAX jednej wartości = ta wartość ..

W takim przypadku możesz również połączyć się 5 razy na podstawie identyfikatora klienta, filtrując według nazwy kolumny kolumny bazy danych według odwołania do tabeli. Może się lepiej udać.


1
to faktycznie nie zadziała, jeśli masz 2 klientów o tym samym imieniu
Leonardo

1
To zadziała. Pamiętaj, że DBColumnName to metadane - dosłownie filtrujesz według „CustomerID = 1 AND DBColumnName = 'FirstName'”. Oczywiście to się psuje, jeśli masz wiele wierszy FirstName dla danego CustomerID, ale jeśli tworzysz swoje tabele poprawnie, zarówno CustomerID, jak i DBColumnName są częścią twojego klucza podstawowego ...
ranem

7
Przykład kodu / kpiny byłby świetny i sprawiłby, że ta odpowiedź byłaby całkowicie kompletna.
DavidScherer,

167

tak ale dlaczego !!??

   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

2
^^ To zadziałało dla mnie. PIVOT nie jest wydajny w przypadku wartości nienumerycznych.
Dienekes

6
To świetna alternatywa. Używałem Pivotw zapytaniu, a potem przełączyłem się na to i przyjrzałem się planowi wykonania obu razem. To podejście kosztowało 8%, a podejście Pivot zajęło 92%!
mafue

2
@CharlesBretana, jesteś świetny! Uratowałeś moją duszę! ) To najlepsze rozwiązanie. Dzięki!
Chaki_Black

3
Naprawdę uwielbiam to rozwiązanie, a także zapewnia, że ​​kolumny zawierają prawidłowe dane zamiast kolumny przestawnej, dzięki!
Tenerezza

2
To działa świetnie! Ale jak mam temu zapobiec -Warning: Null value is eliminated by an aggregate or other SET operation
GiddyUpHorsey,

24
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;

3
Powinna to być akceptowana odpowiedź, ponieważ pokazuje prawidłowe użycie polecenia TSQL Pivot.
Ubercoder

1
Warto zauważyć, że w tym zapytaniu „pivot2” to nazwa tabeli, w której znajdują się oryginalne dane. Również tutaj użycie CTE jest zbędne - SELECTstwierdzenie poniżej CTE mogło po prostu określać nazwę oryginalnej tabeli.
STLDev

@STLDev Właściwie STLDev nie tak działa pivot. Nie znamy wszystkich kolumn w tabeli „pivot2”. W rzeczywistości mogą istnieć inne kolumny, których OP nie określił, a które znajdują się w tabeli. Jeśli więc nie ograniczysz kolumn - używając CTE lub zapytania tabeli pochodnej - w grupowaniu zostaną użyte WSZYSTKIE kolumny w tabeli. Innymi słowy, PIVOT zwraca coś, ale nie to, czego oczekujemy. Jest to koncepcja szczegółowo omówiona podczas egzaminu certyfikacyjnego 70-761.
Zorkolot

2
Warto zauważyć, że PIVOT automatycznie grupuje według tego, jakie kolumny nie są używane w samym PIVOT. W tym przykładzie [data] i [dbcolumnname] znajdują się w PIVOT, więc wszystko zostanie pogrupowane według [CustomerId]
Sal,

9
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' 

Edycja: napisałem to bez edytora i nie uruchomiłem SQL. Mam nadzieję, że masz pomysł.


9

Ok, przepraszam za kiepskie pytanie. gbn doprowadził mnie na właściwą ścieżkę. Właśnie tego szukałem w odpowiedzi.

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

Następnie musiałem użyć instrukcji while i zbudować powyższą instrukcję jako varchar i użyć dynmaic sql.

Używając czegoś takiego

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)

Mając do zbudowania @fulltext za pomocą pętli while i wybrania różnych nazw kolumn z tabeli. Dzięki za odpowiedzi.


6

OP właściwie nie musiał obracać się bez agregacji, ale dla tych z Was, którzy przyjeżdżają tutaj, aby wiedzieć, jak zobaczyć:

sql sparametryzowane zapytanie cte

Odpowiedź na to pytanie dotyczy sytuacji, w której potrzebny jest pivot bez agregacji, więc przykład wykonania tego jest częścią rozwiązania.


1

Spróbuj tego:

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

1

To powinno działać:

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

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

1

Oto świetny sposób na tworzenie dynamicznych pól dla zapytania przestawnego:

--summarize wartości do tabeli tmp

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

--- zobacz wygenerowane pola

print @STr

exec('  .... pivot code ...
pivot (avg(SalesAmt) for DateRange IN (' + @Str +')) AS P
order by Decile')
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.