[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
Medium Priority
223 Views
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

0
Question by:coolnit74
• 2
• 2

LVL 143

Expert Comment

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

ID: 16852276
yes   nn eachtime on the same row
0

LVL 143

Accepted Solution

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

ID: 16852813
Excellentoooooo…   thank you :)
0

## Featured Post

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
Course of the Month19 days, 1 hour left to enroll