CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
mayur Rathod
mayur Rathod

Reputation: 1404

Get column names from first row of SQL Server table

I have 2 tables first table have field names having below structure.

CREATE TABLE [dbo].[FieldNames](
    [SiteId] [int] NULL,
    [Column_1] [nvarchar](50) NULL,
    [Column_2] [nvarchar](50) NULL,
    [Column_3] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL
) ON [PRIMARY]

This table contains below data

1   FirstName   LastName    City    Coutry
2   City        Coutry      Name    Gender

I have another table contains column values having below structure.

CREATE TABLE [dbo].[FieldValue](
    [RowId] [int] NULL,
    [SiteId] [int] NULL,
    [Column1_Value] [nvarchar](50) NULL,
    [Column2_Value] [nvarchar](50) NULL,
    [Column3_Value] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL
) ON [PRIMARY]

This table contains below data.

1   1   ABC     XYZ     Ahmedaad    India
2   1   Hello   Smith   Kodiar  India
3   2   Ahmedaad    India   Happy   data

I want the result something like below for siteid = 1

**FirstName     LastName     City     Coutry**
ABC             XYZ          Ahmedaad India
Hello           Smith        Kodiar   India

Upvotes: 0

Views: 1905

Answers (3)

Squirrel
Squirrel

Reputation: 24773

Do you really need to use such design ? It might be flexible but the coding, developemnt, debugging & maintenance will be a nightmare.

declare @siteid int = 1
declare @sql    nvarchar(max)

select  @sql    = 'SELECT '
        + '[Column1_Value] as ' + quotename([Column_1]) + ', '
        + '[Column2_Value] as ' + quotename([Column_2]) + ', '
        + '[Column3_Value] as ' + quotename([Column_3]) + '  ' + char(13)
        + 'FROM [FieldValue] v' + char(13)
        + 'WHERE v.[SiteId] = @siteid'
from    [FieldNames] n
where   n.SiteId    = @siteid

print   @sql
exec    sp_executesql @sql, N'@siteid int', @siteid

Upvotes: 1

Tyron78
Tyron78

Reputation: 4187

I would suggest to create the corresponding (dynamic) queries via another SQL query and execute these in a loop (or separately if required). The following query will provide you with the dynamic statements:

SELECT DISTINCT fv.[SiteId],
'SELECT     fv.RowId, ' +
'fv.SiteId, ' +
'fv.Column1_Value AS ' + fn.Column_1 + ', ' +
'fv.Column2_Value AS ' + fn.Column_2 + ', ' +
'fv.Column3_Value AS ' + fn.Column_3 + ', ' +
'fv.Name ' +
'FROM [dbo].[FieldValue] fv ' +
'WHERE fv.SiteId = ' + CAST(fv.[SiteId] AS varchar(10)) AS stmt

FROM [dbo].[FieldValue] fv
JOIN [dbo].[FieldNames] fn ON fv.SiteId = fn.SiteId

Upvotes: 0

Sreenu131
Sreenu131

Reputation: 2516

Try this

SELECT [FirstName],
        [LastName],
        [City],
        [Coutry]
FROM [FieldNames] n
INNER JOIN [dbo].[FieldValue] v
    ON n.[SiteId]=v.[SiteId]
CROSS APPLY ( VALUES  (   v.Column1_Value,
                          v.Column2_Value,
                          v.Column3_Value,
                          v.name)
            )
dt ([FirstName],[LastName],[City],[Coutry])
WHERE n.[SiteId]=1

Result

FirstName   LastName    City        Coutry
------------------------------------------
ABC          XYZ        Ahmedaad    India
Hello        Smith      Kodiar      India

Upvotes: 0

Related Questions