SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 select rows horizontal
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cortpcl
Starting Member

3 Posts

Posted - 08/21/2013 :  09:52:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 08/21/2013 :  09:55:38  Show Profile  Reply with Quote
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?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 08/21/2013 :  10:29:32  Show Profile  Reply with Quote
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?)
Go to Top of Page

cortpcl
Starting Member

3 Posts

Posted - 08/21/2013 :  10:50:46  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000