Pivot Unknown Number Of Column Values

Question

I am trying to create an automated data dictionary. I joined all tables' table fields' table and lookup table to get Lookup values (drop down values of fields) as...

+-----------+----------------+---------------+
| TableName | Field Name     | Lookup values |
+-----------+----------------+---------------+
| Pathology | Medical Report | Avaliable     |
| Pathology | Medical Report | Not Avaliable |
| Pathology | Medical Report | Pending       |
+-----------+----------------+---------------+

There are about 200+ tables (TableName) and 5000+ columns (Field Name).

---

Required result

+-----------+----------------+
| TableName | Medical Report |
+-----------+----------------+
| Pathology | Avaliable      |
| Pathology | Not Avaliable  |
| Pathology | Pending        |
+-----------+----------------+

---

So far I have tried the PIVOT function in SQL, but without success as aggregate function can't be applied because no identity column is present.

Extract of my code

SELECT TableName,
       Field1,
       Field2,
       ...,
       Field2000+ (this not possible as there are so many columns)
FROM ( result set
     )
PIVOT
(
    aggregated function doesn't apply as no identity column is present
    FOR ( FieldName ) IN ( Field1,
                           Field2,
                           ...,
                           Field2000+ (this not possible as there are so many columns)
                         )
) AS pivotTable

I do not know exactly how to achieve the required result set.

Can someone please help?

Answer 1

I guess you need something like this:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN;
    DROP TABLE #DataSource;
END;

CREATE TABLE #DataSource
(
    [TableName] SYSNAME
   ,[FieldName] NVARCHAR(64)
   ,[LookupValues] NVARCHAR(64)
);

INSERT INTO #DataSource ([TableName], [FieldName], [LookupValues])
VALUES ('Pathology', 'Medical Report', 'Avaliable')
      ,('Pathology', 'Medical Report', 'Not Avaliable')
      ,('Pathology', 'Medical Report', 'Pending')
      ,('Pathology', 'Laboratory Report', 'Avaliable')
      ,('Pathology', 'Laboratory Report', 'Not Avaliable')
      ,('Pathology', 'Laboratory Report', 'Pending')
      ,('Pathology', 'Laboratory Report', 'Declined')
      ,('Pathology', 'Laboratory Report', 'Private')
      ,('Pathology', 'Laboratory Report', 'Rejected')
      ,('Oncology', 'Laboratory Report', 'Avaliable')
      ,('Oncology', 'Laboratory Report', 'Not Avaliable')
      ,('Oncology', 'Laboratory Report', 'Pending')
      ,('Morgue', 'Death Report', 'Type 1')
      ,('Morgue', 'Death Report', 'Type 2')
      ,('Morgue', 'Death Report', 'Type 3')
      ,('Morgue', 'Death Report', 'Type 4');

DECLARE @DynamicSQLStatement NVARCHAR(MAX)
       ,@PIVOTcolumns NVARCHAR(MAX);

SELECT @PIVOTcolumns = STUFF
(
    (
        SELECT DISTINCT ',[' + [FieldName] + ']'
        FROM #DataSource
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)')
    ,1
    ,1
    ,''
);

SET @DynamicSQLStatement = N'
SELECT *
FROM
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY [TableName], [FieldName] ORDER BY [LookupValues]) AS [RowID]
    FROM #DataSource
) DS
PIVOT
(
    MAX([LookupValues]) FOR [FieldName] IN (' + @PIVOTcolumns + ')
) PVT
ORDER BY [TableName];'

EXEC sp_executesql @DynamicSQLStatement;

Of course, with your sample data, the result will be:

I am sure you can use the code to solve your issue. There is nothing complicated - you need only to build a string with the values on which PIVOT is performed.