February 17, 2017 at 1:16 pm
Hello, I am looking for a way to turn 2 columns into rows. I thought doing a PIVOT would be how to solve this problem but realize there are not enough fields. Using the example below, is there away to list loc information by region in a table?
-- DROP TABLE #Loc
CREATE TABLE #Loc (Region int, Loc int)
INSERT INTO #Loc (Region, Loc) VALUES (1, 9990)
INSERT INTO #Loc (Region, Loc) VALUES (1, 9991)
INSERT INTO #Loc (Region, Loc) VALUES (1, 9992)
INSERT INTO #Loc (Region, Loc) VALUES (1, 9993)
INSERT INTO #Loc (Region, Loc) VALUES (1, 9994)
INSERT INTO #Loc (Region, Loc) VALUES (1, 9995)
INSERT INTO #Loc (Region, Loc) VALUES (2, 9996)
INSERT INTO #Loc (Region, Loc) VALUES (2, 9997)
INSERT INTO #Loc (Region, Loc) VALUES (3, 9998)
INSERT INTO #Loc (Region, Loc) VALUES (3, 9999)
INSERT INTO #Loc (Region, Loc) VALUES (3, 9910)
INSERT INTO #Loc (Region, Loc) VALUES (3, 9911)
INSERT INTO #Loc (Region, Loc) VALUES (4, 9912)
INSERT INTO #Loc (Region, Loc) VALUES (4, 9913)
INSERT INTO #Loc (Region, Loc) VALUES (4, 9914)
INSERT INTO #Loc (Region, Loc) VALUES (5, 9915)
-- SELECT * FROM #Loc
CREATE TABLE #List (Region int, List1 int, List2 int, List3 int, List4 int, List5 int, List6 int)
INSERT INTO #List (Region, List1, List2, List3, List4, List5, List6)
-- QUERY HERE TO TURN COLS INTO ROWS
-- DESIRED REULTS WOULD GO HERE ...
-- Region | List1 | List2 | List3 | List4 | List5 | List6
-- 1 | 9990 | 9991 | 9992 | 9993 | 9994 | 9995
-- 2 | 9996 | 9997 | | | |
-- 3 | 9998 | 9999 | 9910 | 9911 | |
-- 4 | 9912 | 9913 | 9914 | | |
-- 5 | 9915 | | | | |
February 17, 2017 at 1:45 pm
That kind of depends. If you know in advance the largest possible number of locations in a region, it's easiest with what is referred to as a CROSSTAB query. Of course, the reason WHY you want to do it might lead you in another direction. If all you want to do is display these lists of regions, then a couple of other possibilities become practical. Using SSRS (aka Reporting Services) and a tablix or matrix control can give you this kind of display, and a crosstab in Crystal Reports is similar. Also, if having all the locations in a single field would be okay, using a FOR XML PATH construct in your query could flatten out all the locations. Using a PIVOT on an unknown number of values risks losing information because you didn't anticipate the number of possible values, and over time, if that number grows, just keeps requiring you to change your query. Of course, you could use dynamic SQL to solve that problem, but then placing that query as a source into any kind of reporting tool becomes the same problem, just moved into the reporting tool, where it has to be changed to accommodate the new field names that now appear.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 17, 2017 at 2:18 pm
Hi Steve, Thank you for your reply!
What I am really trying to do is:
Take partial data from an import file (UserIDand Region Code)
Integrate the data to work with myinternal tables
Auto-create a row by conforming the receiveddata to my existing table structure
So my source data has the UserID andRegion Code. I have an intermittent table where I have the Region Code andLocation(s) (like sample code #loc) and I want to create a Stored Procedurethat will be able to conform the data to fit into a table structure like #List.
Not sure the best way to do this …
February 17, 2017 at 3:04 pm
rjjh78 - Friday, February 17, 2017 2:18 PMHi Steve, Thank you for your reply!
What I am really trying to do is:
Take partial data from an import file (UserIDand Region Code)
Integrate the data to work with myinternal tables
Auto-create a row by conforming the receiveddata to my existing table structure
So my source data has the UserID andRegion Code. I have an intermittent table where I have the Region Code andLocation(s) (like sample code #loc) and I want to create a Stored Procedurethat will be able to conform the data to fit into a table structure like #List.
Not sure the best way to do this …
Well, the best way is NOT to have a table that flattens locations that way. Why do you need to do that? You can fairly easily write a query that joins on RegionCode and then concatenates a list of the locations as a comma-separated list using FOR XML PATH(''), so what's the objective of the #List table? What is it's purpose?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 17, 2017 at 9:55 pm
rjjh78 - Friday, February 17, 2017 2:18 PMHi Steve, Thank you for your reply!
What I am really trying to do is:
Take partial data from an import file (UserIDand Region Code)
Integrate the data to work with myinternal tables
Auto-create a row by conforming the receiveddata to my existing table structure
So my source data has the UserID andRegion Code. I have an intermittent table where I have the Region Code andLocation(s) (like sample code #loc) and I want to create a Stored Procedurethat will be able to conform the data to fit into a table structure like #List.
Not sure the best way to do this …
If you're saying that the your internal tables look like your desired result set instead of the original data you posted, you might want to consider a redesign to be more like the sample data you posted.
That, notwithstanding, please see the following two articles. The first explains how to use CROSSTABs. The second explains how to dynamically create the code for data driven CROSSTAB structures like the one you posted for your results.
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Using the info from the first article above and your nicely provided test data, here's a non-dynamic solution.
WITH cteEnumerate AS
( --=== Add a column to number the locations for each region
SELECT Region
,Loc = CONVERT(VARCHAR(10),Loc)
,Col# = ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Loc)
FROM #Loc
) --=== "PIVOT" the data based on the column number calculated above.
-- This is called a CROSSTAB. It's ancient but powerful and fast.
SELECT Region
,List1 = MAX(CASE WHEN Col# = 1 THEN Loc ELSE '' END)
,List2 = MAX(CASE WHEN Col# = 2 THEN Loc ELSE '' END)
,List3 = MAX(CASE WHEN Col# = 3 THEN Loc ELSE '' END)
,List4 = MAX(CASE WHEN Col# = 4 THEN Loc ELSE '' END)
,List5 = MAX(CASE WHEN Col# = 5 THEN Loc ELSE '' END)
,List6 = MAX(CASE WHEN Col# = 6 THEN Loc ELSE '' END)
FROM cteEnumerate
GROUP BY Region
ORDER BY Region
;
If that's what you need, let us know and we'll demonstrate the dynamic method from the second article, which can handle a whole lot of diffent numbers of columns auto-magically.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply