Przypisywanie statusów po pivocie

Cześć,

ogólnie temat jest bardziej skomplikowany niz w temacie :)

to moze powklejam co trzeba i napisze co chciałbym osiągnąć.

CREATE TABLE #TabeleWithData(NrSklepu int, Miasto varchar(50),Statu TINYINT)

insert into #TabeleWithData
values
(1,'Warszawa',1),(2,'Warszawa',5),(3,'Warszawa',5),(4,'Warszawa',2),(5,'Warszawa',3),(6,'Warszawa',5),(7,'Warszawa',1),(8,'Warszawa',3),(9,'Warszawa',4),(10,'Warszawa',2)
,(11,'Łódź',4),(12,'Łódź',1),(13,'Łódź',2),(14,'Łódź',3),(15,'Łódź',5),(16,'Łódź',1),(17,'Łódź',3),(18,'Łódź',5),(19,'Łódź',1),(20,'Łódź',3)
,(21,'Poznań',5),(22,'Poznań',2),(23,'Poznań',1),(24,'Poznań',4),(25,'Poznań',3),(26,'Poznań',1),(27,'Poznań',5),(28,'Poznań',3),(29,'Poznań',2),(30,'Poznań',1)

Tabela wygląda mniej więcej tak:

Ccę otrzymać coś takiego:

Więc zrobilem sobie takiego pivota

;WITH CUST_CTE2
AS
(
SELECT NrSklepu,Miasto, ROW_NUMBER() OVER (PARTITION BY (Miasto)ORDER BY NrSklepu) AS RID FROM #TabeleWithData
)
SELECT 
	[Warszawa]
	,[Łódź]
	,[Poznań]
INTO 
	#AfterPivot
FROM
(SELECT NrSklepu,Miasto,RID
FROM CUST_CTE2)C
PIVOT
(
max(NrSklepu)
FOR Miasto IN ([Warszawa],[Łódź],[Poznań])
) AS PivotTable;

Który w efekcie dał mi takie dane:

no i fajnie prawie, ale jak juz wczesniej wspomniałem potrzebuję troche w innym formacie:

udało mi sięuzyskać mój forma w taki sposób

SELECT
	t1.Warszawa
	,t2.Statu AS StatusSklepuZWarszawy
	,t1.Łódź
	,t3.Statu AS StatusSklepuZŁodzi
	,t1.Poznań
	,t4.Statu AS StatusSklepuZWarszawyPoznania
from
	#AfterPivot t1
LEFT JOIN
	#TabeleWithData t2 on t1.Warszawa=t2.NrSklepu
LEFT JOIN
	#TabeleWithData t3 on t1.Łódź=t3.NrSklepu
LEFT JOIN
	#TabeleWithData t4 on t1.Poznań=t4.NrSklepu	

No i wszystko działa ja knależy, ale macie pomysł może ja kzrobić to bez potrzeby kilku Left Joinow?
Bo docelowo to zapytanie ma miec okolo 10 miast co za tym idzie musialbym robic 10 joinow, a nie chce robic czegos na odpierdziel tylko cos przemyslanego.

bede bardzo wdzięczny za pomoc :)

Udało mi się to zrobić jeszcze tak.

CREATE TABLE #TabeleWithData(NrSklepu int, Miasto varchar(50),Statu TINYINT,statusName varchar(25))

insert into #TabeleWithData
values
(1,'Warszawa',1,'StatusWarszawa'),(2,'Warszawa',5,'StatusWarszawa'),(3,'Warszawa',5,'StatusWarszawa'),(4,'Warszawa',2,'StatusWarszawa'),(5,'Warszawa',3,'StatusWarszawa')
,(6,'Warszawa',5,'StatusWarszawa'),(7,'Warszawa',1,'StatusWarszawa'),(8,'Warszawa',3,'StatusWarszawa'),(9,'Warszawa',4,'StatusWarszawa'),(10,'Warszawa',2,'StatusWarszawa')
,(11,'Łódź',4,'StatusŁódź'),(12,'Łódź',1,'StatusŁódź'),(13,'Łódź',2,'StatusŁódź'),(14,'Łódź',3,'StatusŁódź'),(15,'Łódź',5,'StatusŁódź'),(16,'Łódź',1,'StatusŁódź')
,(17,'Łódź',3,'StatusŁódź'),(18,'Łódź',5,'StatusŁódź'),(19,'Łódź',1,'StatusŁódź'),(20,'Łódź',3,'StatusŁódź')
,(21,'Poznań',5,'StatusPoznań'),(22,'Poznań',2,'StatusPoznań'),(23,'Poznań',1,'StatusPoznań'),(24,'Poznań',4,'StatusPoznań'),(25,'Poznań',3,'StatusPoznań')
,(26,'Poznań',1,'StatusPoznań'),(27,'Poznań',5,'StatusPoznań'),(28,'Poznań',3,'StatusPoznań'),(29,'Poznań',2,'StatusPoznań'),(30,'Poznań',1,'StatusPoznań')

;WITH CUST_CTE2
AS
(
SELECT NrSklepu,Miasto,Statu,statusName, ROW_NUMBER() OVER (PARTITION BY Miasto,statusName ORDER BY NrSklepu) AS RID FROM #TabeleWithData
)
select
	[Warszawa]
	,[StatusWarszawa]
	,[Łódź]
	,[StatusŁódź]
	,[Poznań]
	,[StatusPoznań]
from(
SELECT
	RID 
	,[Warszawa]
	,[Łódź]
	,[Poznań]
FROM
(SELECT NrSklepu,Miasto,RID
FROM CUST_CTE2)C
PIVOT( max(NrSklepu)
	FOR Miasto IN ([Warszawa],[Łódź],[Poznań])) AS PivotTable
)s1
LEFT JOIN
	(
	SELECT 
	RID
	,[StatusWarszawa]
	,[StatusŁódź]
	,[StatusPoznań]
FROM
(SELECT Statu,statusName,RID
FROM CUST_CTE2)C
PIVOT
(
max(Statu)
FOR statusName IN ([StatusWarszawa],[StatusŁódź],[StatusPoznań])
) AS PivotTable
) s2 on s1.RID=s2.RID

więc jesli nie macie pomysłów to temat do zamknięcia

Można jednym pivotem:

with cteT as(
select
 miasto
 ,nrsklepu
 ,row_number() over (partition by miasto order by nrsklepu) lp
from
	tabeleWithData
union all
select
 'Status ' + miasto as st
 ,statu
,row_number() over (partition by miasto order by nrsklepu) lp
from
	tabeleWithData
)

select * from ctet
PIVOT (MAX(NrSklepu) FOR Miasto IN ([Warszawa],[Status Warszawa],[Łódź],[Status Łódź],[Poznań],[Status Poznań])) as Sklep

Lub stary, dobry, klasyczny pivot ;)

with cteT as(
select 
*
 ,row_number() over (partition by miasto order by nrsklepu) lp
from
	tabeleWithData)

select 
	max(case when miasto = 'warszawa' then nrsklepu else null end) Warszaw
	,max(case when miasto = 'warszawa' then statu else null end) StatusSklepuZWarszawy
	,max(case when miasto = 'łódź' then nrsklepu else null end) Łódź
	,max(case when miasto = 'łódź' then statu else null end) StatusSklepuZŁodzi
	,max(case when miasto = 'poznań' then nrsklepu else null end) Poznań
	,max(case when miasto = 'poznań' then statu else null end) StatusSklepuZPoznania
 from ctet
group by 
   lp

To wybieram stary klasyczny pivot bez pivota, jako że musze robić z tego dynamiczne zapytanie, czym mnie jkodu tym lepiej :)

Ogólnie dzięki wielkiem :)

Pozdrawiam :)

No ilośc kodu taka sama dla obu zapytań, wersja z pivotem:

declare @sql as nvarchar(max)
select @sql = 
(select distinct ',[' + miasto + '],[Status'+miasto+']' from tabeleWithData for xml path (''),type).value('.', 'NVARCHAR(MAX)')

set @sql = substring(@sql,2,len(@sql)-1)

set @sql = 'WITH cteT AS(
SELECT
 miasto
 ,nrsklepu
 ,ROW_NUMBER() OVER (partition BY miasto ORDER BY nrsklepu) lp
FROM
    tabeleWithData
UNION ALL
SELECT
 ''Status'' + miasto AS st
 ,statu
,ROW_NUMBER() OVER (partition BY miasto ORDER BY nrsklepu) lp
FROM
    tabeleWithData
)
 
SELECT '+@sql+' FROM ctet
PIVOT (MAX(NrSklepu) FOR Miasto IN ('+@sql+')) AS Sklep'

--print @sql
exec sp_executesql @sql

To juz ogarnąłem sam, ale jeszcze raz dzięki :)