Displaying only valid data

Question

Post reply

Displaying only valid data

terry.home

Ten Centuries

Points: 1004
More actions
December 8, 2015 at 5:30 am

#309977

I have a SQL Table structured with columns something like this
A1, A2, A3, B1, B2, B3, C1, C2, C3
Multiple records might be available under each Column
e.g.
A1, A2, A3, B1, B2, B3, C1, C2, C3
01, 02, 03, 00, 00, 00, 00, 00, 00
04, 05, 06, 00, 00, 00, 00, 00, 00
07, 08, 09, 00, 00, 00, 00, 00, 00
Records for each column will mostly be in Either in columns Ax Bx or Cx
e.g.
A1, A2, A3, B1, B2, B3, C1, C2, C3
00, 00, 00, 01, 02, 03, 00, 00, 00
00, 00, 00, 04, 05, 06, 00, 00, 00
00, 00, 00, 07, 08, 09, 00, 00, 00
or
A1, A2, A3, B1, B2, B3, C1, C2, C3
00, 00, 00, 00, 00, 00, 01, 02, 03
00, 00, 00, 00, 00, 00, 05, 05, 06
00, 00, 00, 00, 00, 00, 07, 08, 09
but might occasionally be shown in both.
e.g.
A1, A2, A3, B1, B2, B3, C1, C2, C3
01, 02, 03, 00, 00, 00, 09, 08, 07
04, 05, 06, 00, 00, 00, 06, 05, 04
07, 08, 09, 00, 00, 00, 03, 02, 01
I need to return a report to shows data underneath one another as follows
e.g. (assuming I use last example above)
A1, A2, A3
------------
01, 02, 03
04, 05, 06
07, 08, 09
B1,B2,B3 (since there is no values can be hidden)
-----------
C1,C2,C3
09, 08, 07
06, 05, 04
03, 02, 01
Would really appreciate some thoughts on this if anybody can suggest a way to do this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Could you unpivot the data in the query?

CREATE TABLE #Test(

A1 CHAR(2),

A2 CHAR(2),

A3 CHAR(2),

B1 CHAR(2),

B2 CHAR(2),

B3 CHAR(2),

C1 CHAR(2),

C2 CHAR(2),

C3 CHAR(2)

)

INSERT INTO #Test VALUES

('01', '02', '03', '00', '00', '00', '09', '08', '07'),

('04', '05', '06', '00', '00', '00', '06', '05', '04'),

('07', '08', '09', '00', '00', '00', '03', '02', '01');

SELECT ColGroup, Col1, Col2, Col3

FROM #Test

CROSS APPLY( VALUES ('A', A1, A2, A3),

('B', B1, B2, B3),

('C', C1, C2, C3))u(ColGroup, Col1, Col2, Col3)

ORDER BY ColGroup;

GO

DROP TABLE #Test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

terry.home Ten Centuries Points: 1004 More actions · Answer 2

Thank you for your response. Apologies I had not seen your response. I went down the route of using several selects joined with a union, but will give this a go also just to learn another way of doing it.