6

I have a table like this:

TB1:

COD | A001 | A002 | A003
 1    cars   baby   nasa

and then a second table:

TB2:

COD | NO_COL_TB1 | DESCRIPTION |
 1  |    A001    |  Something
 2  |    A002    |   lasagna

What I'm trying to do is something like this ( obviously this doesnt work )

select A001 as (select description from TB2 WHERE no_col_tb1= A001 )
      ,A002 AS (select description from TB2 WHERE no_col_tb1= A002 )
from TB1

i tried with inner joins and some dynamic sql but I just can't think in a logic for this. There's some questions about this but none of them could help me. I think this is impossible to do with a single statement and only possible with a Dynamic SQL.

EDIT:

The correct result would be:

SELECT COD,
       TB1.A001 AS 'Something',
       TB1.A002 AS 'Lasagna'
FROM TB1

and the result would be:

COD |SOMETHING | LASAGNA |
 1      cars       baby
  • How will the result be consumed? Programmatically? Viewed by humans directly? Dumped into a CSV or Excel file? – jpmc26 Jun 29 at 22:56
  • Please don't write nonsense code & expect us to know what you mean by it. Explain what you want as if you were asking someone to code it--which you are. minimal reproducible example – philipxy Jul 1 at 23:05
  • WHy you think this is a nonsense code? it's all explained in the question, I took the time to edit and explain it even more, also people could understand what I was asking. and I'm not asking for you to code, "I asked if it's possible". Please read the entire question and the body before being rude to someone here. This is not reddit. – Racer SQL Jul 2 at 13:47
25

Sorry to say but your table structure is difficult to work with considering what you want to do. There are various ways you can probably get the result, one way would be to use UNPIVOT and PIVOT, but it's ugly.

You could start by UNPIVOTing the data in TB1 from your columns to rows:

select *
from tb1 t1
unpivot 
(
    val
    for col in (A001, A002, A003)
) u

This is going to return the data in the format of:

| COD |  val |  col |
|-----|------|------|
|   1 | cars | A001 |
|   1 | baby | A002 |
|   1 | nasa | A003 |

Then you could take that result and join it to TB2:

select 
    d.COD,
    d.val,
    t2.DESCRIPTION
from
(
    select *
    from tb1 t1
    unpivot 
    (
        val
        for col in (A001, A002, A003)
    ) u
)d
inner join tb2 t2
    on d.col = t2.NO_COL_TB1;

Which gives the result:

| COD |  val | DESCRIPTION |
|-----|------|-------------|
|   1 | cars |   Something |
|   1 | baby |     lasagna |

Now you have your new column names in the Description and the val in rows, but you want them in columns, so now you can apply the PIVOT function to it:

select *
from
(
    select 
        d.COD,
        d.val,
        t2.DESCRIPTION
    from
    (
        select *
        from tb1 t1
        unpivot 
        (
            val
            for col in (A001, A002, A003)
        ) u
    )d
    inner join tb2 t2
        on d.col = t2.NO_COL_TB1
) x
pivot
(
    max(val)
    for description in (Something, Lasagna)
) p;

This generates the final result you want:

| COD | Something | Lasagna |
|-----|-----------|---------|
|   1 |      cars |    baby |

Now all that is great if you know all the columns that you need to UNPIVOT and then PIVOT, but if you don't then you'll need to use dynamic SQL to solve it. Which will look something like this:

DECLARE 
    @colsUnpivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'tb1' and
                 C.column_name like 'A%'
           for xml path('')), 1, 1, '')

select @colsPivot 
  = stuff((select ','+quotename([DESCRIPTION])
           from Tb2
           for xml path('')), 1, 1, '')


set @query = 'select *
            from
            (
                select 
                    d.COD,
                    d.val,
                    t2.DESCRIPTION
                from
                (
                    select *
                    from tb1 t1
                    unpivot 
                    (
                        val
                        for col in ('+ @colsUnpivot +')
                    ) u
                )d
                inner join tb2 t2
                    on d.col = t2.NO_COL_TB1
            ) x
            pivot
            (
                max(val)
                for description in ('+@colsPivot+')
            ) p;';

exec sp_executesql @query;

It's long, but it should give you the same result. (dbfiddle demo)

4
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF(
    ( SELECT ', ' + QUOTENAME(C.COLUMN_NAME) + ' AS ' + QUOTENAME(F.DESCRICAO)
FROM INFORMATION_SCHEMA.COLUMNS C 
    INNER JOIN TABBASE F ON C.COLUMN_NAME = F.NO_COL_TABBASE
WHERE C.TABLE_NAME = 'TABELA_ENTRADA'
ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')

SELECT @SQL = 'SELECT ' + @SQL + ' FROM tabela_entrada'

PRINT @SQL

EXECUTE(@SQL)

Is there a better way than this? I didn't know I could use STUFF for this. I found this query on this Microsoft page.

0

I've done this when creating some dynamic data for a Word mail merge. The column names need to be known at compile time, so you will need to use dynamic sql.

When you require a lot of columns or columns with large sizes, you will hit the row size limit for the PIVOT function. When this happens you will need to do the pivot using GROUP BY.

DECLARE @Pivot table ( [RowID] int, [ColumnName] nvarchar(50), [ColumnValue] nvarchar(50) );

INSERT INTO @Pivot VALUES (1,'Something','cars'), (1,'lasagna','baby'), (2,'Something','other'), (2,'lasagna','data');

SELECT 
    [RowID],
    [Something] = MAX(CASE WHEN [ColumnName] = 'Something' THEN [ColumnValue] END),
    [lasagna] = MAX(CASE WHEN [ColumnName] = 'lasagna' THEN [ColumnValue] END)
FROM 
    @Pivot
GROUP BY 
    [RowID];

Your data structure requires you to unpivot to join on the table with your column names. I prefer to do this using a cross apply.

DECLARE @TB1 table ( [COD] int, [A001] varchar(50), [A002] varchar(50), [A003] varchar(50) );
DECLARE @TB2 table ( [COD] int, [NO_COL_TB1] varchar(50), [DESCRIPTION] varchar(50) );

INSERT INTO @TB1 values (1,'cars','baby','nasa')
INSERT INTO @TB2 values (1,'A001','Something'), (2,'A002','lasagna')


CREATE TABLE #LargePivotData ( [RowID] int not null, [ColumnName] nvarchar(128) not null, [ColumnValue] nvarchar(max) ); 
-- QUOTENAME will return null for anything longer than 128


INSERT INTO 
    #LargePivotData
SELECT 
    [RowID] = [TB1].[COD],
    [ColumnName] = [DESCRIPTION],
    [ColumnValue] = [TB1_Value]
FROM 
    @TB1 AS [TB1]
    --unpivot
    CROSS APPLY (VALUES
        ('A001', [A001]),
        ('A002', [A002]),
        ('A003', [A003])
    ) AS [TB1_Unpivot]([TB1_Column], [TB1_Value])
    INNER JOIN @TB2 AS [TB2]
        ON [TB1_Unpivot].[TB1_Column] = [TB2].[NO_COL_TB1]


DECLARE @Columns nvarchar(max), @ColumnValues nvarchar(max);

WITH [ColumnNameValues] AS (
    SELECT DISTINCT
        [Name] = QUOTENAME([ColumnName]),
        [Value] = QUOTENAME([ColumnName]) + N' = MAX(CASE WHEN [ColumnName] = N' + QUOTENAME([ColumnName], '''') + N' THEN [ColumnValue] END)'
    FROM 
        #LargePivotData
)
SELECT 
    @Columns = COALESCE(@Columns + N',' + [Name], [Name]),
    @ColumnValues = COALESCE(@ColumnValues + N',' + [Value], [Value])
FROM 
    [ColumnNameValues]
ORDER BY
    [Name];

DECLARE @Sql nvarchar(max) = N'
    WITH [PivotedData] AS (
        SELECT
            [RowID],
            ' + @ColumnValues + N'
        FROM
            #LargePivotData
        GROUP BY
            [RowID]
    )
    SELECT 
        [RowID],
        ' + @Columns + N'
    FROM 
        [PivotedData]
        -- join on any additional tables
    ';

--PRINT @Sql;

DECLARE @params nvarchar(4000) = N''; -- for additional parameters used in @Sql

EXEC sp_executesql @Sql, @params;

DROP TABLE #LargePivotData;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.