sql-serversql-server-2017
One column for each record/ row of a query's result
I have a database with parts that are stored in a table "Part". Each part undergoes a certain measurement (to determine its diameter) that is stored in the table "Measurement". The measurement uses a certain set of rules that are stored in a table "Ruleset". The rules are used to asses the part's category (it is possible that it matches multiple categories). So there is a cross table "Ruleset_x_Category" that's primary key is the combination of the foreign keys "ID_Ruleset" and "Category" to ensure that this combination is unique (each rule set can be combined with each category only ones) that is further connected the rule itself which than holds a Diameter_min- and Diameter_max-Value to check if the part matches the category. The number of rules per rule set varies.
The workflow: you have a part, you measure it, you pick a rule set for the measurement and then you are supposed to get a list that tells you if the part fit's in a number of categories.
Most of this is straight forward. Query for rules, get a "yes/no" if measurement is in between the min/max and so on. What is not easy to me is to get a new column for each row that I get if I query for the (variable number of) rules. Is this possible? And if so what is the technique that I use. Is there a better way to arrange my tables?
SELECT Measurement.id_part, Measurement.diameter, Category.Name, IIF(Measurement.diameter BETWEEN [Rule].Diameter_min AND [Rule].Diameter_max, 'Yes', 'No') As HitsCategory
FROM Measurement INNER JOIN
RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
[Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
Category ON RuleSet_x_Category.id_category = Category.Id
This get's me this:
What I want to have is this:
Larnu gave me the tip to use the pivot keyword. I found this article, however, I have trouble to apply it to my IIF statement. What I have tried:
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the category names
SELECT
@columns+=QUOTENAME([Name]) + ','
FROM
Category
ORDER BY
[Name];
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
SELECT Measurement.id, Measurement.id_part, Measurement.diameter, RuleSet.ID, Category.Name
FROM Measurement INNER JOIN
RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
[Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
Category ON RuleSet_x_Category.id_category = Category.Id
) t
PIVOT(
IIF(Measurement.diameter BETWEEN [Rule].Diameter_min AND [Rule].Diameter_max, ''true'', ''false'')
FOR [Name] IN ('+ @columns +')
) AS pivot_table;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
This gave me the Error:
Msg 195, Level 15, State 1, Line 12
'IIF' is not a recognized aggregate function.
Here is a script to create a demo data base I use to tinker with this problem:
USE [master]
GO
/****** Object: Database [Demo] Script Date: 6/10/2020 2:14:59 PM ******/
CREATE DATABASE [Demo]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Demo', FILENAME = N'C:\Program Files\Demo.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'Demo_log', FILENAME = N'C:\Program Files\Demo.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [Demo] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Demo].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Demo]SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Demo]SET ANSI_NULLS OFF
GO
ALTER DATABASE [Demo]SET ANSI_PADDING OFF
GO
ALTER DATABASE [Demo]SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Demo]SET ARITHABORT OFF
GO
ALTER DATABASE [Demo]SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Demo]SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Demo]SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Demo]SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Demo]SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Demo]SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Demo]SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Demo]SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Demo]SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Demo]SET DISABLE_BROKER
GO
ALTER DATABASE [Demo]SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Demo]SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Demo]SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Demo]SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Demo]SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Demo]SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Demo]SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Demo]SET RECOVERY FULL
GO
ALTER DATABASE [Demo]SET MULTI_USER
GO
ALTER DATABASE [Demo]SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Demo]SET DB_CHAINING OFF
GO
ALTER DATABASE [Demo]SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [Demo]SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [Demo]SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'DemoPivot', N'ON'
GO
ALTER DATABASE [Demo]SET QUERY_STORE = OFF
GO
USE [DemoPivot]
GO
/****** Object: Table [dbo].[Category] Script Date: 6/10/2020 2:14:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](70) NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Measurement] Script Date: 6/10/2020 2:14:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Measurement](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Id_Part] [int] NOT NULL,
[Id_RuleSet] [int] NOT NULL,
[Diameter] [float] NOT NULL,
CONSTRAINT [PK_Measurement] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Part] Script Date: 6/10/2020 2:14:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Part](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](70) NOT NULL,
CONSTRAINT [PK_Part] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Rule] Script Date: 6/10/2020 2:14:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rule](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Diameter_min] [float] NOT NULL,
[Diameter_max] [float] NOT NULL,
CONSTRAINT [PK_Rule] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RuleSet] Script Date: 6/10/2020 2:14:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RuleSet](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_RuleSet] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RuleSet_x_Category] Script Date: 6/10/2020 2:14:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RuleSet_x_Category](
[Id_RuleSet] [int] NOT NULL,
[Id_Category] [int] NOT NULL,
[Id_Rule] [int] NOT NULL,
CONSTRAINT [PK_RuleSet_x_Category] PRIMARY KEY CLUSTERED
(
[Id_RuleSet] ASC,
[Id_Category] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Category] ON
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (1, N'A')
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (2, N'B')
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (3, N'C')
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (4, N'D')
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (5, N'E')
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (6, N'F')
GO
INSERT [dbo].[Category] ([Id], [Name]) VALUES (7, N'G')
GO
SET IDENTITY_INSERT [dbo].[Category] OFF
GO
SET IDENTITY_INSERT [dbo].[Measurement] ON
GO
INSERT [dbo].[Measurement] ([Id], [Id_Part], [Id_RuleSet], [Diameter]) VALUES (1, 1, 1, 12)
GO
SET IDENTITY_INSERT [dbo].[Measurement] OFF
GO
SET IDENTITY_INSERT [dbo].[Part] ON
GO
INSERT [dbo].[Part] ([Id], [Name]) VALUES (1, N'myPart')
GO
SET IDENTITY_INSERT [dbo].[Part] OFF
GO
SET IDENTITY_INSERT [dbo].[Rule] ON
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (1, 10, 12)
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (2, 11, 13)
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (3, 12, 15)
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (4, 13, 18)
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (5, 13, 19)
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (6, 14, 16)
GO
INSERT [dbo].[Rule] ([Id], [Diameter_min], [Diameter_max]) VALUES (7, 15, 17)
GO
SET IDENTITY_INSERT [dbo].[Rule] OFF
GO
SET IDENTITY_INSERT [dbo].[RuleSet] ON
GO
INSERT [dbo].[RuleSet] ([Id], [Name]) VALUES (1, N'myRule')
GO
SET IDENTITY_INSERT [dbo].[RuleSet] OFF
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 1, 1)
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 2, 2)
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 3, 3)
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 4, 4)
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 5, 5)
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 6, 6)
GO
INSERT [dbo].[RuleSet_x_Category] ([Id_RuleSet], [Id_Category], [Id_Rule]) VALUES (1, 7, 7)
GO
ALTER TABLE [dbo].[Measurement] WITH CHECK ADD CONSTRAINT [FK_Measurement_Part] FOREIGN KEY([Id_Part])
REFERENCES [dbo].[Part] ([Id])
GO
ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_Part]
GO
ALTER TABLE [dbo].[Measurement] WITH CHECK ADD CONSTRAINT [FK_Measurement_RuleSet] FOREIGN KEY([Id_RuleSet])
REFERENCES [dbo].[RuleSet] ([Id])
GO
ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_RuleSet]
GO
ALTER TABLE [dbo].[RuleSet_x_Category] WITH CHECK ADD CONSTRAINT [FK_RuleSet_x_Category_Category] FOREIGN KEY([Id_Category])
REFERENCES [dbo].[Category] ([Id])
GO
ALTER TABLE [dbo].[RuleSet_x_Category] CHECK CONSTRAINT [FK_RuleSet_x_Category_Category]
GO
ALTER TABLE [dbo].[RuleSet_x_Category] WITH CHECK ADD CONSTRAINT [FK_RuleSet_x_Category_Rule] FOREIGN KEY([Id_Rule])
REFERENCES [dbo].[Rule] ([Id])
GO
ALTER TABLE [dbo].[RuleSet_x_Category] CHECK CONSTRAINT [FK_RuleSet_x_Category_Rule]
GO
ALTER TABLE [dbo].[RuleSet_x_Category] WITH CHECK ADD CONSTRAINT [FK_RuleSet_x_Category_RuleSet] FOREIGN KEY([Id_RuleSet])
REFERENCES [dbo].[RuleSet] ([Id])
GO
ALTER TABLE [dbo].[RuleSet_x_Category] CHECK CONSTRAINT [FK_RuleSet_x_Category_RuleSet]
GO
USE [master]
GO
ALTER DATABASE [Demo]SET READ_WRITE
GO
(background: I want to use this as a stored procedure from entity framework core in c# that is then the item source of a wpf (mvvm) data grid with a variable number of columns).
Solution
I could not get the values "yes"/"no" or "true"/"false" to be displayed because I could not work around the aggregate function that the PIVOT statement demands.
So I used a CASE statement that outputs 1 or 0, as Joel Coehoorn recommended.
This is my query:
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the category names
SELECT
@columns+=QUOTENAME([Name]) + ','
FROM
Category
ORDER BY
[Name];
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
SELECT Measurement.id_part, Category.Name,
Case
When Measurement.Diameter BETWEEN [RULE].Diameter_min AND [Rule].Diameter_max
THEN 1
Else
0
End As Hit
FROM Measurement INNER JOIN
RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
[Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
Category ON RuleSet_x_Category.id_category = Category.Id
) t
PIVOT(
sum(hit)
FOR [name] IN ('+ @columns +')
) AS pivot_table;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
And this is my result:
I have now three parts, each with a mesurement. If the diameter fits the rules criteria I get a 1, if not a zero. I can work with that.
I have now created a stored procedure that I can (hopefully) run from my app.
CREATE PROCEDURE GetCategories @idmesurement int
AS
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the category names
SELECT
@columns+=QUOTENAME([Name]) + ','
FROM
Category
ORDER BY
[Name];
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
SELECT Measurement.id_part, Category.Name,
Case
When Measurement.Diameter BETWEEN [RULE].Diameter_min AND [Rule].Diameter_max
THEN 1
Else
0
End As Hit
FROM Measurement INNER JOIN
RuleSet ON Measurement.id_ruleset = RuleSet.ID INNER JOIN
RuleSet_x_Category ON RuleSet.ID = RuleSet_x_Category.Id_RuleSet INNER JOIN
[Rule] ON RuleSet_x_Category.id_rule = [Rule].Id INNER JOIN
Category ON RuleSet_x_Category.id_category = Category.Id
WHERE Measurement.id = '+ CONVERT(varchar(10),@idmesurement) +'
) t
PIVOT(
sum(hit)
FOR [name] IN ('+ @columns +')
) AS pivot_table;';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;
With
GetCategories @idmesurement = 1
only outputs the result for my Measurement with the id=1.
Thanks a lot!
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?