asked on

SQL - select data in fields as columns

Hello experts,

Now that I have butchered the title of that question, here's my need:

I have two tables, and I a trying to join them for an output, that may involve a pivot.

Table1: jos_rsform_submission_values:
[FieldName] [FieldValue]
old_coach Smith
played_football_before Yes
emergency_contact_phone 1237890
emergency_contact Mom
father_cell 1234567
mother_guard_name Jane Doe

Table2: jos_rsform_submission_columns
[ColumnName]
old_coach
played_football_before
emergency_contact_phone
emergency_contact
father_cell
mother_guard_name

I would like to end up with:

old_coach played_football_before emergency_contact_phone emergency_contact father_cell mother_guard_name
Smith Yes 1237890 Mom 1234567 Jane Doe
Smith Yes 1237890 Mom 1234567 Jane Doe
Smith Yes 1237890 Mom 1234567 Jane Doe
Smith Yes 1237890 Mom 1234567 Jane Doe

(The above arent dupes, there are many rows in jos_rsform_submission_values etc.)

Something like:

select
*
from
(
select P.[FieldName], P.[FieldValue]
from jos_rsform_submission_values as P
left outer join jos_rsform_submission_columns as PD on PD.[ColumnName] = P.[FieldValue]
) as P
pivot
(
min(P.[FieldName])
for P.[FieldValue] in ([old_coach], [played_football_before], [emergency_contact_phone], [emergency_contact], [father_cell], [mother_guard_name])
) as PIV

Thanks!

ste5an

hmm, what is your table [jos_rsform_submission_columns ] for? Are you looking for a dynamic SQL?

CREATE TABLE #jos_rsform_submission_columns ( ColumnName sysname );
CREATE TABLE #jos_rsform_submission_values
    (
      old_coach NVARCHAR(255) ,
      played_football_before NVARCHAR(255) ,
      emergency_contact_phone NVARCHAR(255) ,
      emergency_contact NVARCHAR(255) ,
      father_cell NVARCHAR(255) ,
      mother_guard_name NVARCHAR(255)
    );

INSERT  INTO #jos_rsform_submission_values
        ( old_coach ,
          played_football_before ,
          emergency_contact_phone ,
          emergency_contact ,
          father_cell ,
          mother_guard_name
        )
VALUES  ( 'Smith' ,
          'Yes' ,
          '1237890' ,
          'Mom' ,
          '1234567' ,
          'Jane Doe'
        );
 
INSERT  INTO #jos_rsform_submission_columns
        ( ColumnName )
VALUES  ( 'old_coach' ),
        ( 'played_football_before' ),
        ( 'emergency_contact_phone' ),
        ( 'emergency_contact' );

DECLARE @Columns NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

SET @Columns = '';
SET @Sql = 'SELECT @Columns FROM #jos_rsform_submission_values;';

SELECT  @Columns = @Columns + ', ' + C.ColumnName
FROM    #jos_rsform_submission_columns C;
SET @Columns = STUFF(@Columns, 1, 2, '');
SET @Sql = REPLACE(@Sql, '@Columns', @Columns);

EXECUTE sys.sp_executesql @Sql;

Open in new window

robthomas09

ASKER

Thanks for the reply Ste5an, here are the create structures for the two tables above:

CREATE TABLE [dbo].[jos_rsform_submission_columns](
      [ColumnName] [varchar](255) NOT NULL
)

CREATE TABLE [dbo].[jos_rsform_submission_values](
      [FieldName] [varchar](max) NOT NULL,
      [FieldValue] [varchar](max) NOT NULL
)

The part im struggling with is getting the values table converted, writing that insert statement you did above for the sample data is essentially what I need to figure out how to script I believe.

Thanks

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robthomas09

ASKER

Thanks!