Get nth NOT NULL Column(s) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get nth NOT NULL Column(s)

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 05:36:48

I have some data to "import" from a staging table which contains "attribute" values in multiple columns

Every possible attribute has its own column (fair enough for the import process)


ID          Font  Colour Size Style Age   
----------- ----- ------ ---- ----- ----- 
1           Arial Red    10   NULL  NULL  
2           Arial Red    12   NULL  NULL  
3           NULL  NULL   NULL A     NULL  
4           NULL  NULL   NULL B     NULL  
5           Arial NULL   NULL NULL  10-12 
6           Arial NULL   NULL NULL  12-14 
7           NULL  Blue   NULL NULL  10-12 
8           NULL  Blue   NULL NULL  12-14

I want to get:

First NOT NULL column as Attribute1
Second NOT NULL column as Attribute2
... up to Four max.
in order to "present" the data to the next stage of processing.

Expected output


ID          Font  Colour Size Style Age   Attr1 Attr2 Attr3 Attr4
----------- ----- ------ ---- ----- ----- ----- ----- ---- -----
1           Arial Red    10   NULL  NULL  Arial Red   10   NULL
2           Arial Red    12   NULL  NULL  Arial Red   12   NULL
3           NULL  NULL   NULL A     NULL  A     NULL  NULL NULL 
4           NULL  NULL   NULL B     NULL  B     NULL  NULL NULL 
5           Arial NULL   NULL NULL  10-12 Arial 10-12 NULL NULL
6           Arial NULL   NULL NULL  12-14 Arial 12-14 NULL NULL
7           NULL  Blue   NULL NULL  10-12 Blue  10-12 NULL NULL  
8           NULL  Blue   NULL NULL  12-14 Blue  12-14 NULL NULL

I can do it with some huge messy CASE statement, any better ideas?

Thanks


SELECT *
INTO #TEMP
FROM
(
	SELECT [ID] = 1, [Font] = 'Arial', [Colour] = 'Red', [Size] = '10', [Style] = NULL, [Age] = NULL
UNION ALL SELECT 2, 'Arial', 'Red', '12', NULL, NULL
UNION ALL SELECT 3, NULL, NULL, NULL, 'A', NULL
UNION ALL SELECT 4, NULL, NULL, NULL, 'B', NULL
UNION ALL SELECT 5, 'Arial', NULL, NULL, NULL, '10-12'
UNION ALL SELECT 6, 'Arial', NULL, NULL, NULL, '12-14'
UNION ALL SELECT 7, NULL, 'Blue', NULL, NULL, '10-12'
UNION ALL SELECT 8, NULL, 'Blue', NULL, NULL, '12-14'
) AS X
GO

SELECT	*
FROM	#TEMP
GO

DROP TABLE #TEMP
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-10 : 05:45:05

quote:
Originally posted by Kristen

I can do it with some huge messy CASE statement, any better ideas?

Yes.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-10 : 06:02:20

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
ID INT,
Font VARCHAR(20),
Colour VARCHAR(20),
Size VARCHAR(20),
Style VARCHAR(20),
Age VARCHAR(20)
)

-- Populate sample data
INSERT @Sample
VALUES (1, 'Arial', 'Red', '10', NULL, NULL),
(2, 'Arial', 'Red', '12', NULL, NULL),
(3, NULL, NULL, NULL, 'A', NULL),
(4, NULL, NULL, NULL, 'B', NULL),
(5, 'Arial', NULL, NULL, NULL, '10-12'),
(6, 'Arial', NULL, NULL, NULL, '12-14'),
(7, NULL, 'Blue', NULL, NULL, '10-12'),
(8, NULL, 'Blue', NULL, NULL, '12-14')

-- Display the wanted result
SELECT s.*,
f.*
FROM @Sample AS s
CROSS APPLY (
SELECT *
FROM (
SELECT 'Attr' + CAST(ROW_NUMBER() OVER (ORDER BY RecID) AS VARCHAR(12)) AS Attr,
Data
FROM (
VALUES (1, s.Font),
(2, s.Colour),
(3, s.Size),
(4, s.Style),
(5, s.Age)
) AS d(RecID, Data)
WHERE Data IS NOT NULL
) AS s
PIVOT (
MAX(Data)
FOR Attr IN ([Attr1], [Attr2], [Attr3], [Attr4])
) AS p
) AS f[/code]

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-06-10 : 06:25:42

Superb! Many thanks, that's a huge improvement. Must learn how to do PIVOT ... but my data is normalised so no real call for it ...