How to get my output in a specific format? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to get my output in a specific format?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-20 : 07:32:14

sql server 2005.

I have a table similar to the one below:-


CREATE TABLE #MyTempTable 
(
recnum INT, 
code1 varchar(6),
code2 varchar(6),
code3 varchar(6),
code4 varchar(6),
code5 varchar(6),
code6 varchar(6),
code7 varchar(6),
code8 varchar(6),
code9 varchar(6),
code10 varchar(6),
code11 varchar(6),
code12 varchar(6)
);
INSERT INTO #MyTempTable VALUES 
(1,'code1','','code3','','','code6','code7','','code9','','code11','code12')
INSERT INTO #MyTempTable VALUES 
(2,'code1','code2','','code4','','code6','code7','code8','','code10','code11','code12')
INSERT INTO #MyTempTable VALUES 
(3,'code1','code2','code3','','code5','code6','','code8','code9','code10','code11','code12')
INSERT INTO #MyTempTable VALUES 
(4,'code1','code2','','code4','code5','code6','','code8','code9','','code11','')
INSERT INTO #MyTempTable VALUES 
(5,'code1','code2','code3','code4','code5','','code7','code8','code9','','','code12')
INSERT INTO #MyTempTable VALUES 
(6,'code1','code2','code3','','','code6','','code8','code9','code10','','code12')
INSERT INTO #MyTempTable VALUES 
(7,'code1','','code3','code4','code5','','code7','code8','','','code11','code12')
INSERT INTO #MyTempTable VALUES 
(8,'code1','code2','','code4','code5','','code7','code8','code9','','code11','code12')
INSERT INTO #MyTempTable VALUES 
(9,'code1','code2','code3','code4','code5','code6','code7','','code9','code10','','code12')
INSERT INTO #MyTempTable VALUES 
(10,'code1','code2','code3','','','','code7','','code9','code10','code11','')
INSERT INTO #MyTempTable VALUES 
(11,'code1','code2','code3','code4','code5','code6','code7','code8','code9','code10','code11','code12')
INSERT INTO #MyTempTable VALUES 
(12,'code1','','code3','code4','','','code7','code8','','code10','code11','code12');

select * from #MyTempTable
order by code1 desc, code2 desc,code3 desc,code4 desc,code5 desc,code6 desc,code7 desc,code8 desc,code9 desc,code10 desc,code11 desc,code12 desc

I would like my output to show something like this:

code1 code2 code3 code4 code5 code6 code7 code8 code9 code10 code11 code12
code1 code2 code3 code4 code5 code6 code7 code8 code9 code10 code11
code1 code2 code3 code4 code5 code6 code7 code8 code9 code10
code1 code2 code3 code4 code5 code6 code7 code8 code9
code1 code2 code3 code4 code5 code6 code7 code8
code1 code2 code3 code4 code5 code6 code7

etc etc

where all where code1 to code12 is not blank show first,
then where code1 to code11 is not blank show next
then where code1 to code10 is not blank show next

etc etc until only code1 is not blank

Could anyone help please as there is more than 10 nested levels for a case statement?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 07:45:31

SELECT * FROM [MyTempTable] WHERE NOT code1 = '' AND NOT code2 = '' AND NOT code3 = ''.....AND Not Code12 = ''
UNION ALL
SELECT * FROM [myTempTable] WHERE NOT code1 = '' AND NOT code2 = '' AND NOT code3 = ''.....AND NOT Code11 = ''
UNION ALL
SELECT * FROM [myTempTable] WHERE NOT code1 = '' AND NOT code2 = '' AND NOT code3 = ''.....AND NOT Code10 = ''
.... and so on.

Your data structure does not make sense.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-20 : 07:47:34

data comes from external source, I have to work with it and produce output.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:32:17

What I think Whitefang meant is that your expected output does not match your sample data.
Please provide some proper sample data and matching expected output.

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

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-20 : 09:07:08

Sorry for not being clear but what I want output to be is:-

If code1 is blank then
if code2 is not blank then move code2 into code1
if code3 is not blank then move code3 into code2
if code4 is not blank then move code4 into code3
if code5 is not blank then move code5 into code4

so for row 1 you get a value in code1,code2,code3,code4.... right up until there are no more values in row one

and then keep on filling the blanks in any row so the output will look something like the output shown in my first post.

Hope this is more clearer

A bit like using COALESCE to fill in the blanks from empty fields with a populated field further up the row

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:13:58

[code]
SELECT RecNum,
code1, code2, code3, code4, code5, code6,
code7, code8, code9, code10, code11, code12
FROM (
SELECT RecNum,
theValue,
'code' + CAST(ROW_NUMBER() OVER (PARTITION BY RecNum ORDER BY CAST(SUBSTRING(theCol, 5, 10) AS INT)) AS VARCHAR(11)) AS theCol,
COUNT(*) OVER (PARTITION BY RecNum) AS i
FROM #MyTempTable
UNPIVOT (
theValue
FOR theCol IN ( code1, code2, code3, code4, code5, code6,
code7, code8, code9, code10, code11, code12)
) AS u
WHERE theValue > ''
) AS d
PIVOT (
MAX(theValue)
FOR theCol IN ( [code1], [code2], [code3], [code4], [code5], [code6],
[code7], [code8], [code9], [code10], [code11], [code12])
) AS p
ORDER BY i DESC,
RecNum[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:26:26

I think I posted a solution for you when you edited your last response.

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

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-20 : 09:44:24

Thanks Peso (again) can the output be ordered so that the contents are populated via the next field's up value?
as running your code i seem to get row1 populated in this order:-

code1, code10, code11, code12, code2, code3, code4, code5, code6, code7, code8, code9

instead of

code1, code2, code3, code4, code5, code6, code7, code8, code9, code10, code11, code12.

Thanks again.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-20 : 09:47:45

Excellent, thank you very much, i get probs with a lot of the data we have coming in from external sources and we are expected to deal with it all.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources