select rows horizontal - 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

General SQL Server Forums

Database Design and Application Architecture

select rows horizontal

Author

Topic

cortpcl
Starting Member

4 Posts

Posted - 2013-08-21 : 09:52:06

Hi,

I have rows in a sql database like this.

The number of rows can vary, also the Label Names can vary.

I need to convert into into new table, so i easier can use them in my asp.net page.

I know .net and c#.
Can anyone give me a hint?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-21 : 09:55:38

quote:
Originally posted by cortpcl

Hi,

I have rows in a sql database like this.

The number of rows can vary, also the Label Names can vary.

I need to convert into into new table, so i easier can use them in my asp.net page.

I know .net and c#.
Can anyone give me a hint?

How wide do you want it to go? If you have 10,000 names in the table, would there be 20,000+ columns ore more?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-08-21 : 10:29:32

Seeing as this is in the design & architecture section, from a design and architecture point of view, this design is really bad as is your output requirement. Can you provide any background into what you want to do and why? Then we'll help you redesign your database so it's a bit more "proper" and also a solution to your output (which is unlikely to be what you need. As James says, what if you have 1000000 rows/columns?)

cortpcl
Starting Member

4 Posts

Posted - 2013-08-21 : 10:50:46

Yes, i know that the data isnt structured very well, unfortunately i cant do anything about that.
I found some code, which i tweaked to my needs.
Ill post for others to use.

create procedure [dbo].[GetFormsubmissions]
@formpageid int
AS
BEGIN

DECLARE @str varchar(max);

SET @str='';

SELECT @str=@str+',MAX(CASE WHEN Label='''+ Label +''' THEN FieldValue ELSE '''' END) AS ['+Label+']'
FROM (SELECT DISTINCT Label FROM [dbo].[FormValues] v INNER JOIN [dbo].[FormSubmissions] s ON s.[id]=v.[SubmissionId] WHERE s.[FormPageId]=@formpageid) AS A;

EXEC('
SELECT SubmissionId'+@str+'
FROM [dbo].[FormValues] v INNER JOIN [dbo].[FormSubmissions] s ON s.[id]=v.[SubmissionId] WHERE s.[FormPageId]=' + @formpageid + '

GROUP BY SubmissionId

');
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources