January 29, 2014 at 10:02 am
Hi all,
Looking for the best way to do write a query.. i have data in a table like this
my_ident | my_val1 | my_val2 | my_val3 | my_val4 | my_val5
1 | red | blue | green | yellow | purple
2 | purple | green | blue | red | pink
3 | blue | black | grey | pink | yellow
4 | ignore | blue | red | green | pink
For the bulk of my query, I want to return it exactly as it is.. but..
If i have 'ignore' in one of the columns, I need to shift all the values to the left, so for my_ident 4
my_val1 = blue, my_val2 = red, my_val3 = green, my_val4 = pink, my_val5 = NULL
Can anyone tell me the easiest way to accomplish this? I don't have any control over the table structure or the values themselves.
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
January 29, 2014 at 10:14 am
Not sure what you mean by not having control over the "values" but wouldn't using CASE to derive the Column values work?
Maybe best to post some ddl and consumable data so someone can have a play and give you a coded response?
January 29, 2014 at 10:20 am
Could you post DDL and sample data in a consumable format?
This should be easy but I don't want to spend half an hour formatting to create a test table with the sample data.
Do you want to display the data or do you want to update it?
January 29, 2014 at 10:26 am
What I mean by not having control over the values is that I can't modify the original data, sorry for the confusion
I've made the ddl below, and made it a little more complicated to explain what i need a little better..
CREATE TABLE temp_1 (
my_ident INT NOT NULL,
my_val1 VARCHAR(50) NULL,
my_val2 VARCHAR(50) NULL,
my_val3 VARCHAR(50) NULL,
my_val4 VARCHAR(50) NULL,
my_val5 VARCHAR(50) NULL
)
INSERT INTO temp_1
SELECT 1 AS my_ident, 'red' AS my_val1, 'blue' AS my_val2, 'green' AS my_val3, 'yellow' AS my_val4, 'purple' AS my_val5 UNION
SELECT 2, 'purple', 'green', 'blue', 'red', 'pink' UNION
SELECT 3, 'blue', 'black', 'grey', 'pink', 'yellow' UNION
SELECT 4, 'ignore', 'blue', 'red', 'green', 'pink' UNION
SELECT 5, 'green', 'blue', 'ignore', 'yellow', 'orange' UNION
SELECT 6, 'yellow', 'red', 'ignore', 'ignore', 'black'
Expected output would be:
my_ident | my_val1 | my_val2 | my_val3 | my_val4 | my_val5
1 | red | blue | green | yellow | purple
2 | purple | green | blue | red | pink
3 | blue | black | grey | pink | yellow
4 | blue | red | green | pink | NULL
5 | green | blue | yellow | orange | NULL
6 | yellow | red | black | NULL | NULL
As you can see, any time there is an ignore, I need to shift the values from the following columns to the left - if ignore is there multiple times, I need to shift it multiple times. This is a simplified version of the table of course, I actually have 15 my_val fields, so would prefer to avoid CASE statements as much as possible as I imagine it'd get pretty crazy quickly as I'd need to check every prior column for multiple ignores, if that makes sense.
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
January 29, 2014 at 10:41 am
You are looking at a very code intensive piece of work to pull this off. What you are trying to do is to actually move columns values from one to another based on some criteria. About the only thing you can do is to build a whole series of nested case expressions.
As I am typing this I do have another idea that might work. Let me put a couple cycles behind this and see if it will work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2014 at 10:41 am
I was thinking on how to do it with a CASE statement, but as you said, the code might have been complicated with multiple ignore and all those columns. I'm sure you know that the best option is to normalize the data but you don't have that option.
Here's a method that unpivots the data and pivots it again without the ignore values.
WITH CTE AS(
SELECT my_ident, my_val, ROW_NUMBER() OVER(PARTITION BY my_ident ORDER BY roworder) rn
FROM temp_1
CROSS APPLY(VALUES(1, my_val1),
(2, my_val2),
(3, my_val3),
(4, my_val4),
(5, my_val5))x(roworder, my_val)
WHERE my_val <> 'ignore'
)
SELECT my_ident,
MAX( CASE WHEN rn = 1 THEN my_val END) my_val1,
MAX( CASE WHEN rn = 2 THEN my_val END) my_val2,
MAX( CASE WHEN rn = 3 THEN my_val END) my_val3,
MAX( CASE WHEN rn = 4 THEN my_val END) my_val4,
MAX( CASE WHEN rn = 5 THEN my_val END) my_val5
FROM CTE
GROUP BY my_ident
If you have questions about the code, feel free to ask and to visit this articles:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
January 29, 2014 at 10:46 am
Sean - its actually for a job that will dump the data into a new simplified table, only running once every week or 2.
Luis - that solution works perfectly - thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply