query for viewing the table in different order

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 2000 Forums

Transact-SQL (2000)

query for viewing the table in different order

Author

Topic

ikrigel
Starting Member

2 Posts

Posted - 2006-10-24 : 04:07:44

QUESTION: HOW DO I CREATE AN SQL QUERY THAT CHANGE THE TABLE\VIEW OR JUST PRESENT IT FROM THE FIRST VIEW TO THE SECOND (THE DESIRED VIEW)?

TABLE 1 – THE CURRENT VIEW:
NEED TO CHANGE
ID1 ID CONNECTION DETAILS
1 11 ADDR1 12345
2 11 ADDR2 23456
3 11 PHONE 02-345678
4 11 FAX 02-456789
5 11 EMAIL A@A.COM
6 22 ADDR1 34567
7 22 ADDR2 45678
8 22 PHONE 03-456789
9 22 FAX 03-567890
10 22 EMAIL B@B.COM

TABLE 2- AFTER WE RUN THE QUERY – THIS IS WHAT I WANT TO GET AS RESULT OF THE SQL (WHICH I DON’T KNOW HOW TO WRITE):
AFTER CHANGES
ID1 ID ADDR1 ADDR2 PHONE FAX EMAIL
1 11 12345 23456 02-345678 02-456789 A@A.COM
2 22 34567 45678 03-456789 03-567890 B@B.COM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 04:23:11

Does SEARCH no work anymore?

SELECT		MIN(ID1) Id1,
		ID,
		MAX(CASE WHEN Connection = 'addr1' THEN details END) 'Addr1',
		MAX(CASE WHEN Connection = 'addr2' THEN details END) 'Addr2',
		MAX(CASE WHEN Connection = 'phone' THEN details END) 'Phone',
		MAX(CASE WHEN Connection = 'fax' THEN details END) 'Fax',
		MAX(CASE WHEN Connection = 'email' THEN details END) 'eMail'
from		MyTable
group by	id

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 04:27:06

Where do you want to show data?
Read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 04:28:48

>>MAX(CASE WHEN Connection = 'addr1' THEN details END) 'Addr1',

Do you need single quote in column alias? (though it will work)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 04:40:28

Easier to read. That's why I use it.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 05:22:00

I prefer using

Select expression as Alias from table

Because it will work in all DBMSs