Options
Need help with a PIVOT in SQL SSMS 2017
AnnieBrown
Posts: 1 New member
in SQL Prompt
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.
I have tried all kinds of pivot examples, but am just getting errors, mainly variations of:
Please help me figure out what I am doing wrong.
Annie :-)
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:
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 :-)
Answers
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017