March 30, 2010 at 2:27 pm
Having some issues trying to figure out how to get the results that I want from the following set of data.
DECLARE @demo
TABLE (
Region char(10),
store_num INTEGER,
store_descchar(10)
);
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1376,'Store 1376');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1378,'Store 1378');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1379,'Store 1379');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1380,'Store 1380');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1383,'Store 1383');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1229,'Store 1229');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1373,'Store 1373');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 4',4817,'Store 4817');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3701,'Store 3701');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 4',4505,'Store 4505');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2461,'Store 2461');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2458,'Store 2458');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2453,'Store 2453');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2448,'Store 2448');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2454,'Store 2454');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2449,'Store 2449');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2651,'Store 2651');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2466,'Store 2466');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 1',1128,'Store 1128');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3403,'Store 3403');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3404,'Store 3404');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2447,'Store 2447');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2411,'Store 2411');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2455,'Store 2455');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 4',4825,'Store 4825');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2402,'Store 2402');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 2',2433,'Store 2433');
INSERT @demo (Region,store_num,store_desc) VALUES ('Region 3',3422,'Store 3422');
select pvt.[Region 1],pvt.[Region 2],pvt.[Region 3],pvt.[Region 4]
from @demo
Pivot (
min(store_desc)
for region in ([region 1],[Region 2],[Region 3],[Region 4])) as pvt
This returns something like this:
Region 1Region 2Region 3Region 4
Store 1128NULLNULLNULL
Store 1229NULLNULLNULL
Store 1373NULLNULLNULL
Store 1376NULLNULLNULL
Store 1378NULLNULLNULL
Store 1379NULLNULLNULL
Store 1380NULLNULLNULL
Store 1383NULLNULLNULL
NULLStore 2402NULLNULL
NULLStore 2411NULLNULL
NULLStore 2433NULLNULL
NULLStore 2447NULLNULL
NULLStore 2448NULLNULL
NULLStore 2449NULLNULL
NULLStore 2453NULLNULL
NULLStore 2454NULLNULL
NULLStore 2455NULLNULL
NULLStore 2458NULLNULL
NULLStore 2461NULLNULL
NULLStore 2466NULLNULL
NULLStore 2651NULLNULL
NULLNULLStore 3403NULL
NULLNULLStore 3404NULL
NULLNULLStore 3422NULL
NULLNULLStore 3701NULL
NULLNULLNULLStore 4505
NULLNULLNULLStore 4817
NULLNULLNULLStore 4825
I would like to return something more like this:
Region 1Region 2Region 3Region 4
Store 1128Store 2402Store 3403Store 4505
Store 1229Store 2411Store 3404Store 4817
Store 1373Store 2433Store 3422Store 4825
Store 1376Store 2447Store 3701
Store 1378Store 2448
Store 1379Store 2449
Store 1380Store 2453
Store 1383Store 2454
Store 2455
Store 2458
Store 2461
Store 2466
Store 2651
Any thoughts?
March 30, 2010 at 3:04 pm
Something like this? (The trick is to assign a number to each row per Region and use this new column to group the data, thus eliminating the null values).
SELECT
MAX(ISNULL(pvt.[Region 1],'')) AS [Region 1],
MAX(ISNULL(pvt.[Region 2],'')) AS [Region 2],
MAX(ISNULL(pvt.[Region 3],'')) AS [Region 3],
MAX(ISNULL(pvt.[Region 4],'')) AS [Region 4]
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Region ORDER BY store_num ) AS ROW,* FROM @demo
)p
PIVOT (
MAX(store_desc)
FOR region IN ([region 1],[Region 2],[Region 3],[Region 4])
) AS pvt
GROUP BY ROW
March 30, 2010 at 3:15 pm
That's exactly what I was looking for!
Thank You.
March 30, 2010 at 3:26 pm
Based on your description and ready to use sample data it was really easy to work on and to match your desired result.
This is one of the very best formats for asking a question that I've seen for quite some time!!
Excellent job on your side! Very well done!
Oh, before I forget: Glad I could help. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply