Turning Rows into Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Turning Rows into Columns

Author

Topic

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-17 : 14:29:04

Hello everyone,

I'm having a brain fart kind of day and my brain hurts (I've been trying to do this for a few hours now).

I have a table that stores information from our forms. The data is stored as rows. So, for example, a form entry of First Name, Last Name, Start Date, End Date (could be NULL) is stored in the table as:

FieldName -- FieldValue
FirstName -- Richard
LastName -- Waugh
StartDate -- 2010/10/25
EndDate -- NULL

What I would like to do is have it displayed as:

FirstName -- LastName -- StartDate -- EndDate

I have queries that pull each entry separately which I thought I could just throw into a temporary table. However, I can't seem to get it done properly.

Any hints or ideas?

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-17 : 15:10:13

First, split the form data into two columns: Name and Value (use charindex and substring for that part)
Second, pivot the derived table on the Name column using MAX(Value) as the aggregate. e.g.


declare @t table (txt varchar(100))
insert into @t(txt) values

('FirstName -- Richard'),
('LastName -- Waugh'),
('StartDate -- 2010/10/25'),
('EndDate -- NULL')

;with 
     src(name,value) as (
		select left(txt, charindex(' -- ', txt)) as name,
		       substring(txt, charindex(' -- ', txt)+4,len(txt)) as Value
		from @t)

select FirstName, LastName, StartDate, EndDate
from src
pivot (max(value) for name in (FirstName, LastName, StartDate, EndDate))pvt

yields:


FirstName	LastName	StartDate	EndDate
Richard	Waugh	2010/10/25	NULL

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-18 : 04:37:00

May be this will help you......



CREATE TABLE #Table (Name VARCHAR(1024),Value VARCHAR(1024))
INSERT INTO  #Table VALUES ('FirstName','Richard'),('LastName','Waugh'),('StartDate','2010/10/25'),('EndDate','NULL'),
						   ('FirstName','Peter'),('LastName','kewin'),('StartDate','2012/1/16'),('EndDate','2013/2/12'),
						   ('FirstName','Jhon'),('LastName','Edward'),('StartDate','2014/06/14'),('EndDate','2014/07/12')
						   
SELECT * FROM #Table
SELECT * FROM (SELECT *,DENSE_RANK()OVER ( Partition BY Name ORDER BY Value)Rn FROM #Table ) A ORDER BY Rn

SELECT [Firstname],[Lastname],[StartDate],[EndDate] FROM 
(SELECT * FROM (SELECT *,DENSE_RANK()OVER ( Partition BY Name ORDER BY Value)Rn FROM #Table ) A Group BY Rn,Name,value
 )a
PIVOT(MIN(Value) FOR Name IN (Firstname,Lastname,StartDate,EndDate)) AS PVT

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-18 : 07:48:27

Little modification to earlier one...



CREATE TABLE #Table (Name VARCHAR(1024),Value VARCHAR(1024))
INSERT INTO  #Table VALUES ('FirstName','Richard'),('LastName','Waugh'),('StartDate','2010/10/25'),('EndDate','NULL'),
						   ('FirstName','Peter'),('LastName','kewin'),('StartDate','2012/1/16'),('EndDate','2013/2/12'),
						   ('FirstName','Jhon'),('LastName','Edward'),('StartDate','2014/06/14'),('EndDate','2014/07/12')

CREATE  TABLE #T(ID INT IDENTITY(1,1),Name VARCHAR(100),Value VARCHAR(100))
INSERT  INTO  #T(Name,Value)  SELECT Name,Value FROM #Table

SELECT Name,Value,Dense_rank()OVER (Partition by name ORDER BY ID)Rn FROM #T

SELECT [Firstname],[Lastname],[StartDate],[EndDate] FROM 
(SELECT Name,Value,Dense_rank()OVER (Partition by name ORDER BY ID)Rn FROM #T) a
PIVOT(MIN(Value) FOR Name IN (Firstname,Lastname,StartDate,EndDate)) AS PVT

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-18 : 08:19:36

Mahaboob Jhonny suggested one more way moreover it's easier than others....



CREATE TABLE #Table (Name VARCHAR(1024),Value VARCHAR(1024))
INSERT INTO  #Table VALUES ('FirstName','Richard'),('LastName','Waugh'),('StartDate','2010/10/25'),('EndDate','NULL'),
						   ('FirstName','Peter'),('LastName','kewin'),('StartDate','2012/1/16'),('EndDate','2013/2/12'),
						   ('FirstName','Jhon'),('LastName','Edward'),('StartDate','2014/06/14'),('EndDate','2014/07/12')


SELECT [Firstname],[Lastname],[StartDate],[EndDate] FROM 
(SELECT *,(Row_Number()OVER ( ORDER BY (SELECT 1))-1)/4 AS Rn FROM #Table) a
PIVOT(MAX(Value) FOR Name IN (Firstname,Lastname,StartDate,EndDate)) AS PVT

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-22 : 08:23:37

Thanks for the replies! I will be attempting to complete this task sometime today and will let you know what the results are. I quickly tried on Friday before leaving for the day but didn't have time to finish.

Thanks again!

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-22 : 10:07:58

Hello again,

I've tried both solutions, both give me errors.

The solution that gbritton indicates that there is an error at the last ")" (Incorrect syntax - I thought by putting a space between the ")" and "pvt" it would work but still get the error).

The last solution that MuralikrishnaVeera posted gives me the following error: "Msg 325, Level 15, State 1, Line 56
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE." I'm assuming changing the compatibility_level will fix this, but haven't tried this yet.

I've also tried a "SELECT * INTO #TempTable FROM ..." solution but still get the errors listed above. Any ideas?

Thanks again!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-22 : 11:09:56

quote:
Originally posted by richardwaugh

Hello again,

I've tried both solutions, both give me errors.

The solution that gbritton indicates that there is an error at the last ")" (Incorrect syntax - I thought by putting a space between the ")" and "pvt" it would work but still get the error).

The last solution that MuralikrishnaVeera posted gives me the following error: "Msg 325, Level 15, State 1, Line 56
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE." I'm assuming changing the compatibility_level will fix this, but haven't tried this yet.

I've also tried a "SELECT * INTO #TempTable FROM ..." solution but still get the errors listed above. Any ideas?

Thanks again!

My query runs successfully on SQL Server 2012 and 2008 R2 (I just copied and pasted into SSMS). What version are you running?

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-22 : 11:20:05

I'm running 2008 R2 as well. I made some modifications previously but just now did a copy and paste and still get the error - "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'."

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-22 : 13:45:48

quote:
Originally posted by richardwaugh

I'm running 2008 R2 as well. I made some modifications previously but just now did a copy and paste and still get the error - "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'."

DO you just hit F5 or highlight the pasted script then hit F5? It's pretty hard to diagnose a problem like this without looking over your shoulder

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-23 : 02:05:44

quote:

Originally posted by MuralikrishnaVeera

Mahaboob Jhonny suggested one more way moreover it's easier than others....



CREATE TABLE #Table (Name VARCHAR(1024),Value VARCHAR(1024))
INSERT INTO  #Table VALUES ('FirstName','Richard'),('LastName','Waugh'),('StartDate','2010/10/25'),('EndDate','NULL'),
						   ('FirstName','Peter'),('LastName','kewin'),('StartDate','2012/1/16'),('EndDate','2013/2/12'),
						   ('FirstName','Jhon'),('LastName','Edward'),('StartDate','2014/06/14'),('EndDate','2014/07/12')


SELECT [Firstname],[Lastname],[StartDate],[EndDate] FROM 
(SELECT *,(Row_Number()OVER ( ORDER BY (SELECT 1))-1)/4 AS Rn FROM #Table) a
PIVOT(MAX(Value) FOR Name IN (Firstname,Lastname,StartDate,EndDate)) AS PVT

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

There is nothing wrong in the query and no complexity as well

When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to next version. When database compatibility is set to previous version and they are attempted with procedure of newer version they will throw above error.

So you need to run this For SQL Server 2008:

EXEC sp_dbcmptlevel 'DatabaseName', 100

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-23 : 08:29:19

quote:
Originally posted by gbritton

quote:
Originally posted by richardwaugh

I'm running 2008 R2 as well. I made some modifications previously but just now did a copy and paste and still get the error - "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'."

DO you just hit F5 or highlight the pasted script then hit F5? It's pretty hard to diagnose a problem like this without looking over your shoulder

What I did was create a new query, copied and pasted and pressed F5. I didn't see anything wrong with your query which is why I am a little confused. I'll be working more on this in the afternoon so once I am done tinkering with things it might work.

Thanks.

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-23 : 08:31:01

quote:
Originally posted by MuralikrishnaVeera

quote:
Originally posted by MuralikrishnaVeera

Mahaboob Jhonny suggested one more way moreover it's easier than others....

CREATE TABLE #Table (Name VARCHAR(1024),Value VARCHAR(1024))
INSERT INTO #Table VALUES ('FirstName','Richard'),('LastName','Waugh'),('StartDate','2010/10/25'),('EndDate','NULL'),
('FirstName','Peter'),('LastName','kewin'),('StartDate','2012/1/16'),('EndDate','2013/2/12'),
('FirstName','Jhon'),('LastName','Edward'),('StartDate','2014/06/14'),('EndDate','2014/07/12')

SELECT [Firstname],[Lastname],[StartDate],[EndDate] FROM
(SELECT *,(Row_Number()OVER ( ORDER BY (SELECT 1))-1)/4 AS Rn FROM #Table) a
PIVOT(MAX(Value) FOR Name IN (Firstname,Lastname,StartDate,EndDate)) AS PVT

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

There is nothing wrong in the query and no complexity as well

When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to next version. When database compatibility is set to previous version and they are attempted with procedure of newer version they will throw above error.

So you need to run this For SQL Server 2008:

EXEC sp_dbcmptlevel 'DatabaseName', 100

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Thanks for the information. We didn't do any upgrades, straight up 2008 R2 from the start. I wasn't the one who set it up though so as I go about my day to day tasks I am finding that I am having to make alterations to the set up at times. I'll give this a shot later and see what happens.

Thanks again.

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-24 : 15:23:39

Hello Murali,

I've gotten your solution to work but do have one question. I've made some modifications to your query as I need to expand it to go with the table I am pulling the data from. This data comes from forms that we complete in our system. This form in particular allows the user to click an "Add" button to add more people. So essentially you could have a form with 3 people listed. I've noticed that when I run the query, if there is only one person listed on the form the results are accurate. However if I have more than one person on the form, the results are not accurate. Below I will post the modified query and also the results.

Any suggestions?

Modified Query

SELECT CONVERT(VARCHAR(1024),cfd.FieldName) AS Name, CONVERT(VARCHAR(1024),cfd.FieldValue) AS Value,cfd.CaseFormID
INTO #TempTable
FROM CaseForms cf
LEFT JOIN CaseFormData cfd ON cf.CaseFormID = cfd.CaseFormID
LEFT JOIN Cases c ON cf.CaseID = c.CaseId
WHERE (cfd.FieldName LIKE '%ddbRelationship%'
OR cfd.FieldName LIKE '%txtStartDate%'
OR cfd.FieldName LIKE '%txtEndDate%'
OR cfd.FieldName LIKE '%txtName%')
AND cf.FormID = 734
ORDER BY cfd.CaseFormID, FieldName
SELECT * FROM #TempTable
SELECT [ddbRelationship],[txtStartDate],[txtEndDate],[txtName] FROM
(SELECT *,(Row_Number()OVER ( ORDER BY (SELECT 1))-1)/4 AS Rn FROM #TempTable) a
PIVOT(MAX(Value) FOR Name IN (ddbRelationship,txtStartDate,txtEndDate,txtName)) AS PVT
DROP TABLE #TempTable

Table Entry (what the temp table looks like):

FieldName -- FieldValue -- CaseFormID
ddbRelationship -- Grandchild -- 467456
ddbRelationshipX_0 -- Grandchild -- 467456
txtEndDate -- *nothing* -- 467456
txtEndDateX_0 -- *nothing* -- 467456
txtName -- John Doe -- 467456
txtNameX_0 -- Jane Doe -- 467456
txtStartDate -- 03/18/2014 -- 467456
txtStartDateX_0 -- 03/18/2014 -- 467456

End Result:

ddbRelationship -- txtStartDate -- txtEndDate -- txtName
Grandchild -- NULL -- *nothing* -- NULL
NULL -- 03/18/2014 -- NULL -- John Doe

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-26 : 01:19:25

Hi richard
when you are not sure about the columns it's better to go for
DYNAMIC PIVOT CONCEPT


CREATE TABLE #TempTable (FieldName VARCHAR(MAX),FieldValue VARCHAR(MAX),CaseFormID INT)
INSERT INTO  #TempTable VALUES   ('ddbRelationship','Grandchild',467456)
							,('ddbRelationshipX_0','Grandchild',467456)
							,('txtEndDate','*nothing*',467456)
							,('txtEndDateX_0','*nothing*',467456)
							,('txtName','John Doe',467456)
							,('txtNameX_0','Jane Doe',467456)
							,('txtStartDate','03/18/2014',467456)
							,('txtStartDateX_0','03/18/2014',467456)

SELECT * FROM #TempTable
----------------------------------------------------------------------
--I Assumed your temp table data like this 
--FieldName -- FieldValue -- CaseFormID
--ddbRelationship -- Grandchild -- 467456
--ddbRelationshipX_0 -- Grandchild -- 467456
--txtEndDate -- *nothing* -- 467456
--txtEndDateX_0 -- *nothing* -- 467456
--txtName -- John Doe -- 467456
--txtNameX_0 -- Jane Doe -- 467456
--txtStartDate -- 03/18/2014 -- 467456
--txtStartDateX_0 -- 03/18/2014 -- 467456
----------------------------------------------------------------------
DECLARE @Col VARCHAR(MAX) = STUFF((SELECT  DISTINCT ','+FieldName FROM #table FOR XML PATH ('')),1,1,'')
SELECT @Col
DECLARE @Query VARCHAR(MAX) = 
N'SELECT CaseFormID,'+@Col+' FROM 
(SELECT * FROM #TempTable) a
PIVOT(MAX(FieldValue) FOR FieldName IN ('+@Col+')) AS PVT'
EXECUTE(@Query)

If this is not the one you are looking for then post expected result
I will try my best to help you

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

richardwaugh
Starting Member

36 Posts

Posted - 2014-04-28 : 09:06:53

Thanks for the reply Murali! I will be attempting your modified solution this afternoon (meetings all morning...) but wanted to post this as it may or may not help. A sample of 2 entries on one form is outlined below as well as the expected output. Also, there could be more than 2 entries on the form (in fact, I can't control how many there are).


Temp table data looks like this:

FieldName           FieldValue          CaseFormId
------------------- ------------------- ----------
ddbRelationship     Grandchild          467456
ddbRelationshipX_0  Grandchild	        467456
txtEndDate          NULL	        467456
txtEndDateX_0       NULL	        467456
txtName	            John Doe            467456
txtNameX_0	    Jane Doe            467456
txtStartDate        03/18/2014          467456
txtStartDateX_0     03/18/2014          467456
...                 ...                 ...
...                 ...                 ...

The output I would like is:

CaseFormID        Name          Relationship          StartDate         EndDate
----------------- ------------- --------------------- ----------------- ------------------
467456            John Doe      Grandchild            03/18/2014        NULL
467456            Jane Doe      Grandchild            03/18/2014        NULL

Thanks,

Richard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources