Combine multiple rows into single row

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 2005 Forums

Transact-SQL (2005)

Combine multiple rows into single row

Author

Topic

yaplej
Starting Member

6 Posts

Posted - 2008-12-10 : 17:57:16

I have this really neat table that stores data from a whole lot of different forms, and fields. I need to make some reports based on this table, but its not in a normal format that I can work with.

This is what I have: I also have a table that has the RowID tied to the FormID.


|-----|---------|----------|
|RowID|FieldName|FieldValue|
|-----|---------|----------|
|1    |Year     |2008      |
|-----|---------|----------|
|1    |Month    |January   |
|-----|---------|----------|
|2    |Year     |2007      |
|-----|---------|----------|
|2    |Month    |March     |
|-----|---------|----------|

This is what I need:


|-----|----|-------|
|RowID|Year|Month  |
|-----|----|-------|
|1    |1008|January|
|-----|----|-------|
|2    |1007|March  |
|-----|----|-------|

I am trying to figure this out, but I think the only way to do this is to use a SP. Something I have never done, but here is what I think needs to happen.

1. Pass SP formID.
2. SP finds all DISTINCT values in strFieldName these will be the columns for the temp table?
3. PS queries strFieldValue for each RowID where strFieldName = each DISTINCT strFieldName to populate the temp table.
4. Display results of the temp table.

Honestly I dont even know if this is possible, but not all forms will have the same fields. Is this even possible? Maybe there is an easier way to get what I need.

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-10 : 18:24:56

select RowID,
Max(case when FieldName = 'Year' then FieldValue else null end)as [Year],
Max(case when FieldName = 'Month' then FieldValue else null end)as [Month]
from table
group by RowID

You can use PIVOT or dynamic PIVOT as well.

yaplej
Starting Member

6 Posts

Posted - 2008-12-10 : 18:49:45

My problem is that I dont ever know what the FieldName is going to be. Maybe dynamic PIVOT as you say is the solution?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-10 : 19:01:14

Then you need this from Madhi's Blog:

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

yaplej
Starting Member

6 Posts

Posted - 2008-12-10 : 22:17:33

Well I got the first part working, but cannot get the 2nd part to work correctly. When I print the @listCol variable it does list all of the column names correctly. I will change this to use take the fkFormID as a parameter later. I still cannot get the PIVOT part to work. What is it missing? The error I get is "Incorrect syntax near 'strFieldName'.


DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT @listCol = STUFF ((SELECT DISTINCT     
		'],[' + dbo.tblResultDetails.strFieldName 
FROM         dbo.tblResults INNER JOIN
                      dbo.tblResultDetails ON dbo.tblResults.pkResultID = dbo.tblResultDetails.fkResultID
WHERE dbo.tblResults.fkFormID = 1
FOR XML PATH('')
				), 1, 2, '') + ']'

SET @query =

'SELECT * FROM
	(SELECT     *
		FROM dbo.tblResultDetails) src
PIVOT strFieldName FOR strFieldValue 

IN ('+@listCol+')) AS pvt'

EXECUTE (@query)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 22:37:49

Since you're trying to pivot multiple columns, what you need is this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

yaplej
Starting Member

6 Posts

Posted - 2008-12-10 : 22:42:24

Wow thats a lot. Thanks though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 02:13:49

welcome

yaplej
Starting Member

6 Posts

Posted - 2008-12-11 : 12:53:56

Am I really trying to PIVOT multiple columns? I want to PIVOT the unique/DISTINCT records in strFieldName. Maybe the code I posted I was trying to PIVOT multiple columns maybe because I didnt really know what it was doing. From what I have read the inner select of @query is the field that will be the PIVOT so I would only want to select the strFieldName then yes?

bhalkett
Starting Member

1 Post

Posted - 2008-12-11 : 16:28:21

Is this what you want?

select r1.RowId,
r1.FieldValue as [Year],
r2.FieldValue as [Month]
From Row r1 INNER JOIN Row r2
ON r1.RowID = r2.RowID
Where r1.FieldName = 'Year'
AND r2.FieldName = 'Month'

Where "Row" is your table name.

yaplej
Starting Member

6 Posts

Posted - 2008-12-11 : 16:38:08

The number, and names of the DISTINCT values from strFieldName will vary based on the formID. So it all has to be dynamic.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 23:52:02

sorry you were right. you need tyo pivot only single column. so this is enough

create table test 
(
RowID int,
FieldName varchar(20),
FieldValue varchar(50)
)

insert into test
select 1    ,'Year','2008' union all
select 1    ,'Month','January' union all
select 2    ,'Year','2007' union all
select 2    ,'Month','March' union all
select 3    ,'Year','2007' union all
select 3    ,'Month','April' union all
select 4    ,'Year','2007' union all
select 4    ,'Month','May' union all
select 5    ,'Year','2006' union all
select 5    ,'Month','March' union all
select 5    ,'Week','27'




DECLARE @FieldNameList varchar(8000),@sql varchar(max)
SELECT @FieldNameList=STUFF((SELECT DISTINCT ',' + FieldName FROM test ORDER BY ','+ FieldName  FOR XML PATH('')),1,1,'')
SELECT @FieldNameList
SET @sql='select *
from
(
select *
from test)m
pivot(max(FieldValue) FOR FieldName IN (['+REPLACE(@FieldNameList,',','],[')+']))p'

EXEC (@Sql)

drop table test


output
---------------------------

(11 row(s) affected)

Month,Week,Year

(1 row(s) affected)

RowID Month Week Year
1 January NULL 2008
2 March NULL 2007
3 April NULL 2007
4 May NULL 2007
5 March 27 2006

(5 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources