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 :-)
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.
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 :-)