|
Tak asi takhle (jestli jsem to správně pochopil). Místo mého CTE si v selectu dosaďte váš zdroj dat.
--CTE pro zdroj dat
;WITH Zdroj (Kod, Obec, Ulica, Orientacne_Supisne_Cislo)
AS
(
SELECT 49 AS Kod, 'Nitrianske Hrnčiarovce' AS Obec, 'Adyho' AS Ulica, '2/602, 4/603, 6/604, 8/605' AS Orientacne_Supisne_Cislo
UNION ALL
SELECT 49, 'Nitrianske Hrnčiarovce', 'Agátová', '1/363, 2/374, 3/364, 4/375, 5/365, 28/722'
UNION ALL
SELECT 49, 'Nitrianske Hrnčiarovce', 'Attilova', '1/491, 1A/732, 2/501, 3/492, 4/502'
UNION ALL
SELECT 4 , 'Nitra', 'Beethovenova', '1/448, 2/450, 3/449, 4/450, 5/449, 6/451'
UNION ALL
SELECT 3 , 'Nitra', 'Partizánska', '64/21, 66/21'
UNION ALL
SELECT 3 , 'Nitra', 'Považská', '2/18, 3/15, 4/18, 5/15'
UNION ALL
SELECT 2 , 'Nitra', 'Viničky', '1/151, 1/37, 3/37'
UNION ALL
SELECT 2 , 'Nitra', 'Viničky', '8, 9/38, 11/38, 13/38, 15/39, 17/39, 19/39, 21/39'
)
SELECT Kod, Obec, Ulica, Orientacne_Supisne_Cislo
FROM (SELECT 1 AS Typ, UPPER(LEFT(Ulica, 1)) AS [Group], CAST(Kod AS nvarchar(5)) AS Kod, Obec, Ulica, Orientacne_Supisne_Cislo
FROM Zdroj
UNION ALL
SELECT 2 AS Typ, UPPER(LEFT(Ulica, 1)) AS [Group], UPPER(LEFT(Ulica, 1)) AS Kod, NULL AS Obec, NULL AS Ulica, NULL AS Orientacne_Supisne_Cislo
FROM Zdroj
GROUP BY UPPER(LEFT(Ulica, 1))
) Sub
ORDER BY [Group], Typ DESC
Vrací:
A NULL NULL NULL
49 Nitrianske Hrnčiarovce Adyho 2/602, 4/603, 6/604, 8/605
49 Nitrianske Hrnčiarovce Agátová 1/363, 2/374, 3/364, 4/375, 5/365, 28/722
49 Nitrianske Hrnčiarovce Attilova 1/491, 1A/732, 2/501, 3/492, 4/502
B NULL NULL NULL
4 Nitra Beethovenova 1/448, 2/450, 3/449, 4/450, 5/449, 6/451
P NULL NULL NULL
3 Nitra Partizánska 64/21, 66/21
3 Nitra Považská 2/18, 3/15, 4/18, 5/15
V NULL NULL NULL
2 Nitra Viničky 1/151, 1/37, 3/37
2 Nitra Viničky 8, 9/38, 11/38, 13/38, 15/39, 17/39, 19/39, 21/39
|