Urgent help !

Last Post 13 Feb 2008 03:44 AM by SwePeso. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
victor
New Member
New Member

--
11 Feb 2008 10:52 AM
Guys, I really need help with this one.
I am populating the data warehouse and have to deal with some bad sourse databases.
In one table they have 28 columns, used as flags. Each column represents a particular facility. Each record can have 0 to many facilities selected, so this table could have NULL or 'Y' in one or many of those columns. That's a terrible DB design. They should have build a 1-to-many table relationship, but instead, they put everything in one table. And the other funny thing is that those 28 columns are literally named Column1, Column2 ... Column28, and then the actual facility names are HARDCODED in their application codes !!!
So, the table might look like this:
Col1 Col2 Col3 Col4 Col5 .... Col28
--------------------------------------------------
NULL Y NULL NULL Y NULL
Y NULL NULL NULL NULL NULL
NULL Y NULL NULL NULL Y
...........
NULL Y NULL NULL Y Y

My task is to get for each record the first 4 not NULL facility NAMES. I have a script that does that, but this script is very primitive and is huge ! I kind of do 4 runs through this file. First run I get the record ID for each record, and populate the first of the 4 facility names. I am just using CASE statement and check each field if it's NOT NULL then enter the actual hardcoded name. I put all this into a #temp table. The second run I join the main table with this #temp table, again getting the record ID and the first populated Facility Column 1, and then create a Second Facility field, by using the CASE statement again, going through each field, checking for NOT NULL AND NOT being the same as the First Facility name... And so on... Like I said, this is working, but I was just wondering is there is a simpler way of doing this.

Any help or suggestion will be greatly appreciated.

Thanks,
Victor.
SQLUSA
New Member
New Member

--
11 Feb 2008 03:06 PM
Try the COALESCE function.


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/BI2005WorkShop/ The Best SQL Server 2005 Training in the World!
victor
New Member
New Member

--
11 Feb 2008 05:56 PM
And how would I use it ? What exactly would it give me ?
SwePeso
New Member
New Member

--
11 Feb 2008 11:18 PM
It would give you nothing.
Even though I have not yet received the set of 26-disc for review, I can certainly come up with a better idea.

DECLARE @Sample TABLE (RowID INT IDENTITY(1,1), Col1 CHAR(1), Col2 CHAR(1), Col3 CHAR(1), Col4 CHAR(1), Col5 CHAR(1), Col6 CHAR(1))

INSERT @Sample
SELECT NULL, 'Y', NULL, NULL, 'Y', NULL UNION ALL
SELECT 'Y', NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT NULL, 'Y', NULL, NULL, NULL, 'Y' UNION ALL
SELECT NULL, 'Y', NULL, NULL, 'Y', 'Y'

-- Show the expected result normalized
SELECT u.RowID,
u.theCol
FROM (
SELECT p.RowID,
p.theCol,
ROW_NUMBER() OVER (PARTITION BY p.RowID ORDER BY CAST(SUBSTRING(p.theCol, 4, 2) AS INT)) AS RecID
FROM @Sample AS s
UNPIVOT (
theValue
FOR theCol IN (s.Col1, s.Col2, s.Col3, s.Col4, s.Col5, s.Col6)
) AS p
) AS u
WHERE u.RecID <= 4
ORDER BY u.RowID,
u.RecID

-- Show the expected result denormalized
SELECT p.RowID,
p.[1],
p.[2],
p.[3],
p.[4]
FROM (
SELECT p.RowID,
p.theCol,
ROW_NUMBER() OVER (PARTITION BY p.RowID ORDER BY CAST(SUBSTRING(p.theCol, 4, 2) AS INT)) AS RecID
FROM @Sample AS s
UNPIVOT (
theValue
FOR theCol IN (s.Col1, s.Col2, s.Col3, s.Col4, s.Col5, s.Col6)
) AS p
) AS s
PIVOT (
MAX(s.theCol)
FOR s.RecID IN ([1], [2], [3], [4])
) AS p
victor
New Member
New Member

--
12 Feb 2008 05:56 AM
Thank you, Peter.
It worked. !!!

Victor.
SQLUSA
New Member
New Member

--
12 Feb 2008 04:14 PM
Pretty brilliant work Peter!


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/BI2005WorkShop/ The Best SQL Server 2005 Training in the World!
SwePeso
New Member
New Member

--
13 Feb 2008 03:44 AM
Another normalized approach

SELECT s.RowID,
x.ColumnName
FROM @Sample AS s
CROSS APPLY (
SELECT TOP 4 'Col' + CAST(d.ColumnName AS VARCHAR) AS ColumnName
FROM (
SELECT 1 AS ColumnName, s.Col1 AS ColumnValue UNION ALL
SELECT 2, s.Col2 UNION ALL
SELECT 3, s.Col3 UNION ALL
SELECT 4, s.Col4 UNION ALL
SELECT 5, s.Col5 UNION ALL
SELECT 6, s.Col6
) AS d
WHERE d.ColumnValue IS NOT NULL
ORDER BY d.ColumnValue
) AS x

You are not authorized to post a reply.

Acceptable Use Policy