SQL - Append cell/row output to existing column name

Question

I'm not sure if this is possible exactly, but I'm looking for a way to take a value from a row (one row table) and append it to another column's name in MS SQL Server.

Example:

Table A:

  [Document Count], [File Size]
    -------------------------
    50, 100 GB

Result:

[Document Count (100 GB)]
------------------------
50

As you can see I'm adding the row value to the header name of another column.

I have several columns I want to do this for in an automated way so I'm trying to avoid having to type it out each time.

I appreciate the help.

sacse · Accepted Answer · 2020-04-26 09:57:24Z

1

You can try the following:

DECLARE @cols AS VARCHAR(MAX), @query AS VARCHAR(MAX);

SET @cols = STUFF((select distinct ', 
            MAX(CASE WHEN [Document Count]=''' + CAST([Document Count] as varchar(10)) + ''' THEN [Document Count] ELSE 0 END) AS [Document Count ' + CAST([File Size] as varchar(10)) +']'
                        /*---------------------------------you can add other columns similar to the above here----------------------------------*/
            FROM #t 
            FOR XML PATH(''),type).value('.','varchar(max)'),1,2,'')

exec ('SELECT [Document Count], [File Size], ' + @Cols +'  FROM #t group by [Document Count], [File Size]')

Please find the db<>fiddle here.

answered Apr 26, 2020 at 9:57

sacse

3,8272 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KGRed · Accepted Answer · 2020-04-26 12:45:58Z

DECLARE @t1 nvarchar(255), @cname nvarchar(255), @sqlcom nvarchar(2000), @s nvarchar(255)
select @cname = c.name from sys.columns c where c.object_id = object_id('table_A','U') and c.name like '%document_count%';
select @t1 = [filesize] from table_A;
set @s = '['+@cname+'_'+@t1+']';
set @sqlcom = 'select document_count as '+@s+' from table_A;'
exec (@sqlcom);

As you mentioned that there are multiple columns in the table, in such case you can use this procedure to extract values from all other columns(except your desired - 'document_count') and concatenate to the first column name. check the below:

create table table_A
(
    document_count int,
    filesize varchar(50)
);
GO

insert table_A values(50,'100 GB');
GO

CREATE PROCEDURE dbo.colnamegen (
/*
DECLARE @outparam NVARCHAR(255)
EXEC dbo.colnamegen 'table_A','%document_count%',@ColumnValues = @outparam OUTPUT;
*/
    @tablename NVARCHAR(255),
    @columnname NVARCHAR(255),
    @ColumnValues NVARCHAR(255) OUTPUT)
AS
BEGIN

DECLARE 
        @colname NVARCHAR(25),
        @AllColList NVARCHAR(255),
        @tmpstr NVARCHAR(255),
        @SqlCommand NVARCHAR(2500),
        @collist NVARCHAR(1000),
        @dynsql NVARCHAR(2500)

-- extracting column name '%document_count%'
SELECT @colname = c.name FROM sys.columns c WHERE c.object_id = OBJECT_ID(@tablename,'U') AND name like @columnname;

--A temperory string for removing the first column from all column list.
SET @tmpstr = ' + ISNULL( '+QUOTENAME(@colname)+' ,'' '') + ';

-- column list
SELECT @collist = SUBSTRING((select ', '+QUOTENAME(c.name)
                                FROM sys.columns c
                                WHERE c.object_id = OBJECT_ID('table_A')--(@tablename)
                                FOR XML PATH('')),3, 1000);

-- A list of all columns in the table excluding the first column
-- which will used in dynamic query to perform concatenation of all the values of all
-- columns of table.
SELECT @AllColList = REPLACE((select ' + ISNULL( ' + QUOTENAME(c.name) + ' ,'' '')'
                                FROM sys.columns c
                                WHERE c.object_id = OBJECT_ID(@tablename)
                                FOR XML PATH('')),@tmpstr,'');

--values from all the columns are concatenatied and assigned to a variable
SET @SqlCommand = '
        SELECT @ColumnValues = a.newcol 
        FROM(
            SELECT '+@AllColList+' AS newcol FROM '+@tablename+
            ')a;'

EXEC sp_executesql @SqlCommand, N'@ColumnValues nvarchar(255) OUTPUT', @ColumnValues = @ColumnValues OUTPUT;

--subsequent and final query for desired output
SET @dynsql = 'SELECT '+@collist+', '+@colname+' AS '+'['+@colname+@ColumnValues+']'+' FROM '+@tablename+';'
EXEC (@dynsql);
END;
GO

DECLARE @outparam NVARCHAR(255)
EXEC dbo.colnamegen 'table_A','%document_count%',@ColumnValues = @outparam OUTPUT;
GO

Collectives™ on Stack Overflow

SQL - Append cell/row output to existing column name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related