Convert Rows into Column in Sql Server

sarfaraz ali

11y1.3kSQL

Hi ,

How To convert Rows To column iN SQL Without Using Povit & Aggregate function ????

I have use this Query (select DE.name, DV.value from dbo.DemographicsElementSet as DESET, dbo.DemographicsElement as DE, dbo.DataValue as DV where DESET.demElementSetGroupId ='6F2E5F4B-22CF-4AFF-9B6E-C4856B1D086E' and DE.demElementId = DESET.demElementRef and DV.valueId = DE.valueRef)

result display Like

name	value
Country City State Contact Mobile First Name Gender Specialization Post Code Contact LandLine Middle Name Address Last Name HospitalName	03 28754 283108 khan sarfaraj Male 1003 GZB single 01/01/1992 8800636868 ali 03

I want output as

name	Country C ity State ...............
value	03 28754 283108

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

sarfaraz ali11y ago
thanks All
Roshan Patil11y ago
This is nothing but Transporse of your result

You need to use PIVOT functionality of SQL to get the solution

As your number of rows and country in it may vary at different type you need to create a dynamic query and execute it.

Below is your query.

declare @names VARCHAR(MAX)

SELECT @names = COALESCE(@names +'],[' ,'[') + name
FROM
(SELECT DISTINCT convert(varchar,name) as name FROM DemographicsElementSet ) map

select @names =@names +']'

DECLARE @QUERY VARCHAR(8000)

SET @QUERY='SELECT '+@names+ '
FROM
(SELECT DISTINCT name,value FROM DemographicsElementSet ) AS ST
PIVOT
(
MAX(value)
FOR name IN ('+@names+')
) AS PT'

EXEC (@QUERY)

PIVOT works only for SQL SERVER 2005 and above
Govinda Rajulu Yemineni11y ago
Hi,

please refer the following link

http://www.aspdotnet-suresh.com/2012/09/how-to-convert-rows-to-columns-in-sql.html
Manas Mohapatra11y ago
Hi sarfaraz,

Please try below query using pivot:

CREATE TABLE yt
([name] varchar(20), [value] varchar(20));

INSERT INTO yt
([name], [value])
VALUES
('Country', '03'),
('City', '28754'),
('Gender', 'Male'),
('Address', '8800636868'),
('FirstName', 'Khan')
;

select *
from
(
      select [name], [value]
      from yt
) src
pivot
(
   max(value)
   for [name] in ([Country], [City], [Gender], [Address], [FirstName])
) piv;