Change Data display - 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

General SQL Server Forums

New to SQL Server Programming

Change Data display

Author

Topic

bhasi75
Starting Member

2 Posts

Posted - 2011-07-22 : 08:31:41

I presently have the data as shown below

ID P1N P1D P2N P2D P3N P3D <-- column names
01 00A AAA 000 000 000 000
02 00B BBB 00A AAA 00C CCC
03 00B BBB 00A AAA 000 000

I want to display the above data as below

ID PN0 PD0 <-- column names
01 00A AAA
02 00B BBB
02 00A AAA
02 00C CCC
03 00B BBB
03 00A AAA

How do i achieve this using SQL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-22 : 08:48:04

In the select list you can give an alias to each column name.
select
P1N as PN0,
P1D as PD0,
...
from ...

No, you're never too old to Yak'n'Roll if you're too young to die.

bhasi75
Starting Member

2 Posts

Posted - 2011-07-22 : 08:58:50

webfred,

please look at the data, i.e. the data for ID 02 which is in one row should be displayed in three rows as it is having data in (P1N & P1D) , (P2N & P2D) AND (P3N & P3D)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-22 : 09:07:25

Would union all work for you - like this:


SELECT ID, P1N, P1D WHERE P1N <> '000' AND P1D <> '000' UNION ALL
SELECT ID, P2N, P2D WHERE P2N <> '000' AND P2D <> '000' UNION ALL
SELECT ID, P3N, P3D WHERE P3N <> '000' AND P3D <> '000'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources