how to transpose rows to columns

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  March 25, 2011 at 9:37 pm

  #247908

  Hi,

  Here is table DDL

  create table #temp

  (

  flightname varchar(20)

  ,segmentname varchar(20)

  ,depatureplace varchar(20)

  ,arrivalplace varchar(20)

  )

  insert into #temp

  select 'KL123','kl123a','aaaa','bbbb'union all

  select 'KL123','kl123b','bbbb','cccc'union all

  select 'KL123','kl123c','cccc','dddd'union all

  select 'KL126','kl126a','aaaa','bbbb'union all

  select 'KL126','kl126b','bbbb','dddd'union all

  select 'KL128','kl128','aaaa','dddd'

  select * from #temp

  flightnamesegmentnamedepatureplacearrivalplace

  KL123kl123aaaaabbbb

  KL123kl123bbbbbcccc

  KL123kl123cccccdddd

  KL126kl126aaaaabbbb

  KL126kl126bbbbbdddd

  KL128kl128aaaadddd

  here flight source is aaaa and destination is dddd

  KL123 flight has 3 segments(aaaa-bbbb ,bbbb-cccc,cccc-dddd)

  KL126 flight has 2 segments(aaaa-bbbb ,bbbb-dddd)

  KL128 has only one segment(aaaa-dddd)

  segment means Break journeys

  Expected output:

  flightname,seg1_segmentname,seg1_depatureplace,seg1_arrivalplace,seg2_segmentname,seg2_depatureplace,seg2_arrivalplace,seg3_segmentname,seg3_depatureplace,seg3_arrivalplace

  KL123kl123aaaaabbbbkl123bbbbbcccckl123cccccdddd

  KL126kl126aaaaabbbbkl126bbbbbddddnullnullnull

  KL128kl128aaaaddddnullnullnullnullnullnull

  here flight can has at most 10 segments.

  Please help me in a query.

  Thanks

• WayneS

  SSC Guru

  Points: 95460

  More actions

  March 25, 2011 at 10:14 pm

  #1303926

  Please check out the two articles in my signature: CrossTabs and Pivot Tables, Parts 1 and 2. This should give you all the information you need for how to do this.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  March 25, 2011 at 10:29 pm

  #1303929

  Thank you WayneS

  you are article is helpful for me