[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Qurey output display in different format

Posted on 2006-06-07
4
Medium Priority
?
223 Views
Last Modified: 2008-02-26
Hi

I have a  simple query  which gives output as on column . Is there a way to split the output into multiple columns.

Select Distinct MACH  From Test_table with(nolock)
Where Left(Mach,1) = 'T'or Left(Mach,1) = 'U' or Left(Mach,1) = 'X'

Result :

====

Mach

T01
T02
T03
U01
U02
U03
X01
X02
X03

I want the output in this format

=====
Mach1    Mach2     Mach3
T01         U01        X01
T02         U02        X02
T03         U03        X03

Please help me out ..:)




0
Comment
Question by:coolnit74
  • 2
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16852237
how are the values related, will it be Tnn, Unn and Xnn , with nn each time on the same row?
0
 

Author Comment

by:coolnit74
ID: 16852276
yes   nn eachtime on the same row
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16852326
select 'T' + t.value as mach1, 'U' + u.value as mach2, 'X' + x.value as mach3
from ( Select distinct substring(Mach,2,50) as Value ) as L
left join ( Select substring(Mach,2,50) as Value From Test_table with(nolock) Where Left(Mach,1) = 'T' ) as t
  on t.value = l.value
left join ( Select substring(Mach,2,50) as Value From Test_table with(nolock) Where Left(Mach,1) = 'U' ) as u
  on u.value = l.value
left join (Select substring(Mach,2,50) as Value From Test_table with(nolock) Where Left(Mach,1) = 'X'  ) as x
  on x.value = l.value
0
 

Author Comment

by:coolnit74
ID: 16852813
Excellentoooooo…   thank you :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question