-1

I have scenario, where i need to convert rows to columns, but we do not have the fixed # of rows for each customer, it depends on how much fields he/she enters the values based on that those rows will be created in the table. Suppose we have 15 fields on the form, and he/she fill only 13 then, only 13 rows will be created for that customer with values and if some customer fill out all 15 fields then i will have 15 rows in the table for that customer.

Could you please help on how i can write query on this varying rows list and how i can write the sql query to convert all rows to columns with single query.

Sample Data:
Id      EntityId  Key        Value
1          1      Fname       john
2          1      lname       smith
3          1      city        nyc
4          1      state       ny
5          1      degree      MBA
6          2      Fname       john
7          2      lname       smith
8          2      city        nyc
9          2      state       ny

if you see above data, entityid=1 have 5 records and 2 only 4.

Thanks

M.Ali
  • 67,945
  • 13
  • 101
  • 127
user2986108
  • 95
  • 1
  • 2
  • 7
  • 1
    This question appears to be off-topic because it is asking for code. Questions asking for code must demonstrate a minimal effort by showing attempted code or research. Many similar questions exist on StackOverflow with the subject "rows into columns." These questions will show how to use dynamic SQL with pivot. – Kermit Feb 12 '14 at 21:54

2 Answers2

1

Test Data

DECLARE @MyTable TABLE (Id INT,EntityId INT,[Key] VARCHAR(20),Value  VARCHAR(20))
INSERT INTO @MyTable VALUES
(1,1,'Fname' ,'john'),
(2,1,'lname' ,'smith'),
(3,1,'city'  ,'nyc'),
(4,1,'state' ,'ny'),
(5,1,'degree','MBA'),
(6,2,'Fname' ,'john'),
(7,2,'lname' ,'barbar'),
(8,2,'city'  ,'Mexico city'),
(9,2,'state' ,'MC')

Query

SELECT *
FROM
(
SELECT EntityId, [Key],Value FROM @MyTable)t
PIVOT(MIN(Value)
      FOR [Key]
      IN ([Fname],[lname],[city],[state],[degree])
      )p

Result Set

╔══════════╦═══════╦════════╦═════════════╦═══════╦════════╗
║ EntityId ║ Fname ║ lname  ║    city     ║ state ║ degree ║
╠══════════╬═══════╬════════╬═════════════╬═══════╬════════╣
║        1 ║ john  ║ smith  ║ nyc         ║ ny    ║ MBA    ║
║        2 ║ john  ║ barbar ║ Mexico city ║ MC    ║ NULL   ║
╚══════════╩═══════╩════════╩═════════════╩═══════╩════════╝
M.Ali
  • 67,945
  • 13
  • 101
  • 127
-2

Maybe take a look at this.

SQL Pivot and Unpivot.

http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

Bob
  • 66
  • 4
  • Link answers are not very helpful. You can improve your answer by posting a sample of a pivot in action. – Kermit Feb 13 '14 at 01:36