Need help with a PIVOT in SQL SSMS 2017

AnnieBrown

Hi all,

I have tried everything i can think of and all I do is move around the error codes.  Please help!

I am trying to run a query that lists the column name, whatever else data we choose and then columns for "Sensitivity" and "Information Type", but I cannot get the values for "sensitivity" and "information type" to display on only one row.

Sensitivity        Information Type      ColumnName

NULL               Financial                   DisburseAmt

Confidential     NULL                        DisburseAmt

NULL               Financial                   FstMtgAmt

Confidential     NULL                        FstMtgAmt

Confidential     NULL                        GrossAmount

NULL               Financial                   GrossAmount

Here is my query that created the results above:

<div>USE [Redgate_Classification]
GO
SELECT CASE
           WHEN TagCategories.Name = 'Sensitivity' THEN
               Tags.Name
       END AS Sensitivity,
       CASE
           WHEN TagCategories.Name = 'Information Type' THEN
               Tags.Name
       END AS [Information Type],
       ClassificationSuggestions.ColumnTableSchemaDatabaseName,
       ClassificationSuggestions.ColumnTableSchemaName,
       ClassificationSuggestions.ColumnTableName,
       ClassificationSuggestions.ColumnName
FROM dbo.ColumnTags
    INNER JOIN dbo.Tags
        ON ColumnTags.TagId = Tags.Id
    INNER JOIN dbo.ClassificationSuggestions
        ON ColumnTags.TagId = ClassificationSuggestions.TagId
           AND ColumnTags.ColumnName = ClassificationSuggestions.ColumnName
           AND ColumnTags.ColumnTableName = ClassificationSuggestions.ColumnTableName
           AND ColumnTags.ColumnTableSchemaName = ClassificationSuggestions.ColumnTableSchemaName
    INNER JOIN dbo.TagCategoryAssetTypeApplications
        INNER JOIN dbo.TagCategories
            ON TagCategoryAssetTypeApplications.TagCategoryId = TagCategories.Id
        ON Tags.CategoryId = TagCategories.Id
ORDER BY ClassificationSuggestions.ColumnTableName,
         ClassificationSuggestions.ColumnName;<br></div><div></div>

I have tried all kinds of pivot examples, but am just getting errors, mainly variations of:

Select [Information Type], [Sensitivity], ColumnTableSchemaDatabaseName, ColumnTableSchemaName, ColumnTableName, ColumnName

FROM
(
SELECT        
TagCategories.Name AS Category, 
Tags.Name AS Tag, 
ClassificationSuggestions.ColumnTableSchemaDatabaseName,     
ClassificationSuggestions.ColumnTableSchemaName, 
ClassificationSuggestions.ColumnTableName, ClassificationSuggestions.ColumnName
FROM 
ColumnTags 
INNER JOIN
Tags ON ColumnTags.TagId = Tags.Id 
INNER JOIN
ClassificationSuggestions ON ColumnTags.TagId = ClassificationSuggestions.TagId 
AND ColumnTags.ColumnName = ClassificationSuggestions.ColumnName 
AND ColumnTags.ColumnTableName = ClassificationSuggestions.ColumnTableName 
AND ColumnTags.ColumnTableSchemaName = ClassificationSuggestions.ColumnTableSchemaName 
INNER JOIN
TagCategoryAssetTypeApplications 
INNER JOIN TagCategories 
ON TagCategoryAssetTypeApplications.TagCategoryId = TagCategories.Id 
ON Tags.CategoryId = TagCategories.Id
PIVOT
(MAX(Tags.Name)
FOR TagCategories.Name 
IN ("Information Type", "Sensitivity"))
as PIVOTTABLE

Please help me figure out what I am doing wrong.
Annie  :-)

KBSingh

Try this link:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

KBSingh

Try this link:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017