October 13, 2010 at 9:03 am
I have a situation where I have data that looks like the following:
ABC NULL NULL NULL NULL
NULL DEF NULL NULL NULL
NULL GHI NULL NULL NULL
NULL NULL JKL NULL NULL
NULL NULL NULL MNO NULL
NULL NULL NULL NULL PQR
and I want it to look like
ABC DEF JKL MNO PQR
NULL GHI NULL NULL NULL
or
ABC DEF JKL MNO PQR
ABC GHI JKL MNO PQR
If it was such that we had one non-null value per row, I could just do a select top 1 where not null for each column and get the values I want, like described in the second option here:
http://www.sqlservercentral.com/Forums/Topic372024-149-1.aspx
It's that column with 2 (or more) values that's causing me such fits. Anyone have any idea how to do this?
October 13, 2010 at 9:16 am
I can only imagine a design & requirement like that can be due to a serious flaw, most likely lack of normalisation. This is a horrible problem to solve. Can you describe your real problem rather than an abstracted one? It may be that there is a real world solution that is much clearer.
The only thing I can think of for what you have described is a hellishly ugly thing with five derived tables, all using ROW_NUMBER() and FULL OUTER JOINS between them. This would return your first result set.
Also, I don't see how your second desired result set could work for more complex data (e.g. three or more values in the columns).
October 13, 2010 at 9:17 am
BTW - I'll wait in the hope someone else can think of a better solution before I try writing the abortion of a query I have in mind....
October 13, 2010 at 10:50 am
I would suggest un-pivoting the data, selecting what isn't null, and then pivoting it back.
The two articles in my signature block on Cross-Tabs and Pivot tables (parts 1 and 2) might be of assistance to you. You might also want to check out the UNPIVOT operator in BOL.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 11:01 am
Quirky update is probably the cleanest way out of this, but it's not going to be pretty. I'd like to see your original DDL / proper sample data, please. Check the first link in my sig if you need help with that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 13, 2010 at 11:51 am
markpruett_02 (10/13/2010)
I have a situation where I have data that looks like the following:ABC NULL NULL NULL NULL
NULL DEF NULL NULL NULL
NULL GHI NULL NULL NULL
NULL NULL JKL NULL NULL
NULL NULL NULL MNO NULL
NULL NULL NULL NULL PQR
and I want it to look like
ABC DEF JKL MNO PQR
NULL GHI NULL NULL NULL
or
ABC DEF JKL MNO PQR
ABC GHI JKL MNO PQR
If it was such that we had one non-null value per row, I could just do a select top 1 where not null for each column and get the values I want, like described in the second option here:
http://www.sqlservercentral.com/Forums/Topic372024-149-1.aspx
It's that column with 2 (or more) values that's causing me such fits. Anyone have any idea how to do this?
Does the data have to remain in it's original column?
Or is this acceptable:
ABC DEF GHI JKL MNO
PQR NULL NULL NULL NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 12:51 pm
Well, it's a vendor's database we're looking at, and they have a whole bunch of result values in the same table in code/value pairs.
Our folks want them reported on in separate fields - i.e. code as the column name, and the result as the actual data.
It wouldn't be so awful except that in the application that this data comes from, they can enter multiple values for the same measurement, so I may have 2 of the same code with different values.
For example, there may be a field named "Corrective Action", with 2 entries- "Replace Ball Joint" and "Align Control Arm". In the db, both would be code 32, for "Corrective Action", but with the different entries as the values.
It's probably not a wholly terrible way to organize their tables for the application, but it's downright awful for reporting back out!
Thanks for your help- I was hoping there was a relatively easy way to do it; I have an idea for an abortion of a query that I suppose I could try, but I'm not very enthusiastic about it.
October 13, 2010 at 1:38 pm
mark - is respect to what Craig said earlier:
Craig Farrell (10/13/2010)
Quirky update is probably the cleanest way out of this, but it's not going to be pretty. I'd like to see your original DDL / proper sample data, please. Check the first link in my sig if you need help with that.
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Also, you didn't answer my question:
Does the data have to remain in it's original column?
Or is this acceptable:
ABC DEF GHI JKL MNO
PQR NULL NULL NULL NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 13, 2010 at 2:25 pm
This can get you:
abc def jkl mno pqr
abc ghi jkl mno pqr
select distinct t1.c1, t2.c2, t3.c3, t4.c4, t5.c5
from testtbl t1
cross join testtbl t2
cross join testtbl t3
cross join testtbl t4
cross join testtbl t5
where
t1.c1 is not null and
t2.c2 is not null and
t3.c3 is not null and
t4.c4 is not null and
t5.c5 is not null
October 13, 2010 at 2:44 pm
keith.gerritsen (10/13/2010)
This can get you:abc def jkl mno pqr
abc ghi jkl mno pqr
select distinct t1.c1, t2.c2, t3.c3, t4.c4, t5.c5
from testtbl t1
cross join testtbl t2
cross join testtbl t3
cross join testtbl t4
cross join testtbl t5
where
t1.c1 is not null and
t2.c2 is not null and
t3.c3 is not null and
t4.c4 is not null and
t5.c5 is not null
That is functional, I'll give you that. I certainly hope though that he's not processing a lot of data or his system's going to go into the corner and cry if it's not a monster box.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply