Transpose of Data

Transpose of Data

• Ozberg

  HI both the above techniques are OK.... I agree... My requirement is like I am using SQl server 2000... on more you can think any database.... SQl, Oracle,MySQL etc.... is there any generic SQL statement which can do the job .... because all the above solutions i have worked out. you are perfectly allright Umachandar that there are other issues too.....

  Basically this was asked at some interview to me that you don't have capabilities of reporting tools.... plus you are not supposed to use pivot functinality...plus not by any programming script.... just do it using single SQl :)

  and being having 3 years of experience in SQL development... according to me atleast in SQL server , I was unable to devise such a query....

  My motive is simple if is there any way.... please come up with that simple statement...

  thanks in advance

  mandip

  
  
  

• MaqboolHussain

  have a look at the PIVOT function....u'll get what u want
  
  
  

• David Zokaites

  You can use PIVOT operator in SQL Server 2005.

  select pt.[1], pt.[2]....

  from t

  pivot (max(name) for empno in ([1], [2], [3], [4] .... )) as pt

  In older versions of SQL Server, you can use a standard SQL statement like:

  select

  max(case t.empno when 1 then t.name end) as [1]

  , max(case t.empno when 2 then t.name end) as [2]

  , max(case t.empno when 3 then t.name end) as [3]

  ...

  from t

  Please note that both these techniques require you to fix the pivot column lists. If you need it to be dynamic then the solution is more complicated using the current SQL Server facilities and there are other issues. Typically, you would generate this sort of report on the client side where there are numerous reporting capabilities based on the tool you are using.

  
  
  
• Transpose of Data