How to Convert Rows into Columns using SQL Select Query?

Question

Master Table

Code   UserName

1      UserOne

2      UserTwo

3      UserThree

Details Table

Code  UserCode  ParamName  ParamValue

1  1  NameOne  ValueOne

1  1  NameTwo  ValueTwo

1  1  NameThree  ValueThree

and so on

The above is my Master and Details table. I wanna write a query which will convert the rows of details table into columns. The desired output is given below:

Code  UserCode  NameOne  NameTwo  NameThree     and so on

1  1  ValueOne  ValueTwo  ValueThree     and so on

How can I achieve this? Any suggestion will be great in advance.

possible duplicate of [Efficiently convert rows to columns in sql server 2008](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008) — GarethD, Apr 28 '14 at 16:59

score 0 · Answer 1 · answered Apr 28 '14 at 16:23

This is a common problem, without know anyting about BDMS to use, i suggest two low-level solution:

adding column by JOIN
adding column by subselect

Adding column by subselect consist in adding a subselect to take each dato do you need to traspose into column.

Adding column by JOIN consist into add a left join to data (whit right data cut), ad expose field you need into columns.

These solution are each static and valid only if you have a fixed number of column to traspsose. A way to let that dinamy could be to intruduce a store procedure.

score 0 · Answer 2 · answered Apr 28 '14 at 16:27

0

I hope the below query will help you achieve what you want

 SELECT DISTINCT
    (SELECT ParamValue FROM tblDetails WHERE ParamName ='TestOne' AND UserCode = tb.UserCode) AS TestOne,
    (SELECT ParamValue FROM tblDetails WHERE ParamName ='TestTwo' AND UserCode = tb.UserCode) AS TestTwo,
    (SELECT ParamValue FROM tblDetails WHERE ParamName ='TestThree' AND UserCode = tb.UserCode) AS TestThree
 FROM tblDetails tb

or you can use PIVOT if the ParamName can have many values which cannot be guaranteed in advance.

answered Apr 28 '14 at 16:27

Manish Dalal

1,768
1
10
14

ParamName can have many values. It's not fixed. Any idea without pivot? – MITHUN Apr 28 '14 at 16:34
1

any option of using any application or dynamic queries in SQL Stored procedure? – Manish Dalal Apr 28 '14 at 16:37

score 0 · Answer 3 · answered Apr 28 '14 at 16:42

0

A few general / common strategies...

You can use a PIVOT query...

You can use a CASE (TSQL) or DECODE (PLSQL) type of statement...

SELECT
   ...
   CASE Parmname WHEN 'NameOne' THEN [ValueOne] ELSE '' END as NameOne,
   CASE Parmname WHEN 'NameTwo' THEN [ValueTwo] ELSE '' END as NameTwo
   ...

You can use DERIVED TABLES...

SELECT
   ...
   N1.Parmname,
   N2.Parmname,
   ...
FROM
   ...
   LEFT JOIN (SELECT * FROM tbl_Detail WHERE Parmname = 'NameOne') N1   
   ON...
   LEFT JOIN (SELECT * FROM tbl_Detail WHERE Parmname = 'NameTwo') N2
   ...

...etcetera

answered Apr 28 '14 at 16:42

Chains

12,541
8
45
62

I can't hard code ParamName as it is dynamic. Any other idea? – MITHUN Apr 28 '14 at 16:49
@MITHUN -- If you don't know what the columns will be ahead of time, then you would have to build the query dynamically (build a sql string piece-by-piece using other queries or a WHILE loop, etc., and then executing it afterward). This can get a bit complicated... – Chains Apr 28 '14 at 16:52
@MITHUN -- what are you trying to achieve / what will consume the output of this query? (maybe there is an easier / non-sql approach?) – Chains Apr 28 '14 at 16:54

How to Convert Rows into Columns using SQL Select Query?

3 Answers3