Przypisywanie statusów po pivocie

leonkuczma

2017-03-28T21:40:43+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 68

0

Cześć,

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

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

Kopiuj

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:

NrSklepu	Miasto	Status
1	Warszawa	1
2	Warszawa	5
3	Warszawa	5
4	Warszawa	2
5	Warszawa	3
11	Łódź	4
12	Łódź	1
13	Łódź	2
14	Łódź	3
15	Łódź	5
21	Poznań	5
22	Poznań	2
23	Poznań	1
24	Poznań	4
25	Poznań	3

Ccę otrzymać coś takiego:

Warszawa	StatusSklepuZWarszawy	Łódź	StatusSklepuZŁodzi	Poznań	StatusSklepuZWarszawyPoznania
1	1	11	4	21	5
2	5	12	1	22	2
3	5	13	2	23	1
4	2	14	3	24	4
5	3	15	5	25	3
6	5	16	1	26	1
7	1	17	3	27	5
8	3	18	5	28	3
9	4	19	1	29	2
10	2	20	3	30	1

Więc zrobilem sobie takiego pivota

Kopiuj

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

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

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

Kopiuj

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 :)

leonkuczma

2017-03-28T22:24:21+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 68

0

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

Kopiuj

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

Panczo

2017-03-28T22:52:45+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3902

1

Można jednym pivotem:

Kopiuj

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

Kopiuj

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

leonkuczma

2017-03-28T23:16:29+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 68

0

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 :)

Panczo

2017-03-28T23:37:25+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 3902

0

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

Kopiuj

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