CODEWITHSUNDEEP
sql-serversql-server-2008-r2sql-server-2012
Ydouwant2know
Ydouwant2know

Reputation: 29

Joining Table Row and table Column in SQL

I have a table called Tbl with n number of columns with n number of rows.

Create Table Tbl(Col1, Col2, Col3,...)

I have another table called TblColNms in which I am maintaining column names.

Create Table TblColNms(ColId, ColNms)

In TblColNms, I insert the column names of table 'Tbl'. For example:

Insert into TblColNms(1, Col1)
Insert into TblColNms(2, Col2)

My requirement is:

To retrieve only the columns which was inserted in TblColNms.
For ex.:

Select (Select ColNms 
        from TblColNms) 
from Tbl

Which should return all rows from Tbl but with only those 2 column names.

Upvotes: 0

Views: 1032

Answers (4)

Chanoch
Chanoch

Reputation: 591

You can get SQL Server to convert a series of row values to a comma delimited list by using FOR XML PATH('') as follows:

SELECT ',' + colName from ColumnNames for XML path('')

This would give something like ,first_column,another_column,last_column

If you do this within a declaration, you can execute the resulting statement much like eval() works in javascript using sp_executesql

I used STUFF here to delete the first comma

DECLARE @SQLQuery NVARCHAR(MAX) = 'SELECT ' 
           + STUFF((
                SELECT ',' + colName from ColumnNames for XML path('')
             ), 1, 1, '') + ' FROM Product';
EXEC sp_executesql @SQLQuery

Upvotes: 0

shA.t
shA.t

Reputation: 16978

As I understand you need a result of row-set by some special column names those stored in a table:

  1. You need a Stored Procedure that can give your results statically, like this:
CREATE PROCEDURE SP1 AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @query as nvarchar(max) = 'SELECT '
    SELECT @query = @query + ColNms + ','
    FROM TblColNms

    SET @query = LEFT(@query, LEN(@query) - 1 ) + ' FROM Tbl'
    EXEC(@query)
END
GO

And you will have your result by executing it like this:

EXEC SP1
  1. Make a table like Temp1 and query over it like this:
CREATE PROCEDURE SP2 AS
BEGIN
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp1]') AND type in (N'U'))
        DROP TABLE [dbo].[Temp1]

    DECLARE @query as nvarchar(max) = 'SELECT '

    SELECT @query = @query + ColNms + ','
    FROM TblColNms

    SET @query = LEFT(@query, LEN(@query) - 1 ) + ' INTO dbo.Temp1 FROM Tbl'
    EXEC(@query)
END

And when you want your result use it like this:

EXEC SP2

SELECT * FROM Temp1

This way will give you more options like adding conditions and etc. to your results like:

SELECT Top(<n>) * 
FROM Temp1 
WHERE <condition>

Upvotes: 0

user4622594
user4622594

Reputation:

maybe something like this:

DECLARE @TABLE_NAME NVARCHAR(MAX)
DECLARE @CMD NVARCHAR(MAX)
SET @TABLE_NAME = 'dbo.Table1'
SET @CMD = 'SELECT '

SELECT @CMD = @CMD + ',' + ColNms
FROM TblColNms

--eliminate leading comma
SET @CMD = REPLACE(@CMD,' ,',' ') + ' FROM ' + @TABLE_NAME

PRINT @CMD
EXEC sp_executesql @CMD

Upvotes: 0

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

This looks like you are storing data in form of stack. You can use PIVOT with dynamic query to achieve this.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Year, ' + @ColumnName + '
    FROM #CourseSales
    PIVOT(SUM(Earning) 
          FOR Course IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

Original author: http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

Upvotes: 2

Related Questions