Convert Rows into Column in Sql Server

Question

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

sarfaraz ali · Answer

thanks All

Roshan Patil · Answer

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 Yemineni · Answer

Hi, please refer the following link http://www.aspdotnet-suresh.com/2012/09/how-to-convert-rows-to-columns-in-sql.html

Manas Mohapatra · Answer

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;

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

Convert Rows into Column in Sql Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (4)