SQL Server - convert rows into columns

Asked By Hozefa Unwala on 16-Nov-11 11:05 AM

I have something this kind of data

name constructionName TotalPayment RemainingPayment
Mahesh LivingRoom 10000 3000
Mahesh Bedroom 20000 5000
Mahesh Diningroom 5000 1000
Ashok LivingRoom 12000 2000
Ashok Bedroom 25000 4000
Nimesh Diningroom 20000 12000

I have this kind of data. Now I want to conver this data from rows to column.
like Each person name has only one entry

Mahesh LivingRoom 10000 3000 Bedroom 20000 5000 diningroom 5000 1000

Ashok Living room 12000 2000 Bedroom 25000 4000 diningroom 0 0
Nimesh Livingroom 0 0 Bedroom 0 0 dining room 20000 12000

How do i get this data?
Please help me out.
Thanks.

Kirtan Patel replied to Hozefa Unwala on 16-Nov-11 12:09 PM
suppose you have two columns Month and Sales and you want to tranform them you can do it like below
 
DECLARE @salesTable TABLE
(
   [month] INT,
   sales INT
)

-- Note that I use SQL Server 2008 INSERT syntax here for inserting
-- multiple rows in one statement!
INSERT INTO @salesTable
VALUES (0, 2) ,(0, 2) ,(1, 2) ,(1, 2) ,(2, 2)
   ,(3, 2) ,(3, 2) ,(4, 2) ,(4, 2) ,(5, 2)
   ,(6, 2) ,(6, 2) ,(7, 2) ,(8, 2) ,(8, 2)
   ,(9, 2) ,(10, 2) ,(10, 2) ,(11, 2) ,(11, 2)
 
-- How to perform Tranformation

SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
FROM
(
   SELECT [month], sales
   FROM @salesTable
) AS SourceTable
PIVOT
(
   SUM(sales)
   FOR [month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable
 
 
Hozefa Unwala replied to Hozefa Unwala on 16-Nov-11 12:11 PM
but what will happen if I have more than 1 column?
Neha Garg replied to Hozefa Unwala on 16-Nov-11 01:15 PM
Hello, Hozefa,

For example, we are considering a table names Ashish


want to perform a sql query to return results like this:


Here is SQL Query to convert Rows into Columns:

Declare @t VARCHAR(10)
Declare @A VARCHAR(1000)
Declare @B VARCHAR(1000)
set @A='SELECT CustID'
SET @B='('
SELECT @A=@A+',['+Question+'] as [' +Question+']',@B=@B+'['+Question+'],' FROM (SELECT DISTINCT QUESTION FROM ashish) cur
-- removing last ',' from both variables
SET @B=SUBSTRING(@B,1,LEN(@B)-1)
SET @A=@A+ + ' FROM (SELECT CustID, Answer, Question FROM ashish) s  PIVOT (max(answer) FOR Question IN ' +@B+')) p ORDER BY [CUSTID]; '
exec(@A);


Suchit shah replied to Hozefa Unwala on 17-Nov-11 01:00 AM
heck out a sample at,


http://www.umachandar.com/technical/SQL6x70Scripts/Main25.htm


which transposes Columns as Rows


SELECT CASE WHEN [RowID] = 1 THEN [Value] ELSE NULL END AS [1],
CASE WHEN [RowID] = 2 THEN [Value] ELSE NULL END AS [2], 
CASE WHEN [RowID] = 3 THEN [Value] ELSE NULL END AS [3] 
FROM YourTable


SQL Server Helper
http://www.sql-server-helper.com/