Technetshadow

Technetshadow

  • NA
  • 148
  • 7.7k

Complex PIVOT SQL Query Help needed

Nov 7 2014 1:09 AM
Below is the data, i Want a SQL to get Device Count grouped by Owner & then by Country
as shown in ResultTable (resultset)
DB Script:

CREATE TABLE [dbo].[tblState](
[StateId] [int] NULL,
[StateName] [varchar](100) NULL,
[CountryId] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[tblState] ([StateId], [StateName], [CountryId]) VALUES (1, N'State1', 1)
INSERT [dbo].[tblState] ([StateId], [StateName], [CountryId]) VALUES (2, N'State2', 1)
INSERT [dbo].[tblState] ([StateId], [StateName], [CountryId]) VALUES (3, N'State3', 2)
INSERT [dbo].[tblState] ([StateId], [StateName], [CountryId]) VALUES (4, N'State4', 3)

CREATE TABLE [dbo].[tblOwner](
[OwnerId] [int] NULL,
[OwnerName] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[tblOwner] ([OwnerId], [OwnerName]) VALUES (1, N'O1')
INSERT [dbo].[tblOwner] ([OwnerId], [OwnerName]) VALUES (2, N'O2')
INSERT [dbo].[tblOwner] ([OwnerId], [OwnerName]) VALUES (3, N'O3')
INSERT [dbo].[tblOwner] ([OwnerId], [OwnerName]) VALUES (4, N'O4')

CREATE TABLE [dbo].[tblDevice](
[DeviceId] [int] NULL,
[DeviceName] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[tblDevice] ([DeviceId], [DeviceName]) VALUES (1, N'Server1')
INSERT [dbo].[tblDevice] ([DeviceId], [DeviceName]) VALUES (2, N'Server2')
INSERT [dbo].[tblDevice] ([DeviceId], [DeviceName]) VALUES (3, N'Server3')

CREATE TABLE [dbo].[tblDataTable](
[DeviceId] [int] NULL,
[OwnerId] [int] NULL,
[StateId] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tblDataTable] ([DeviceId], [OwnerId], [StateId]) VALUES (1, 1, 1)
INSERT [dbo].[tblDataTable] ([DeviceId], [OwnerId], [StateId]) VALUES (1, 2, 4)
INSERT [dbo].[tblDataTable] ([DeviceId], [OwnerId], [StateId]) VALUES (2, 1, 1)
INSERT [dbo].[tblDataTable] ([DeviceId], [OwnerId], [StateId]) VALUES (3, 3, 2)

CREATE TABLE [dbo].[tblCountry](
[CountryId] [int] NULL,
[CountryName] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
INSERT [dbo].[tblCountry] ([CountryId], [CountryName]) VALUES (1, N'Country1')
INSERT [dbo].[tblCountry] ([CountryId], [CountryName]) VALUES (2, N'Country2')
INSERT [dbo].[tblCountry] ([CountryId], [CountryName]) VALUES (3, N'Country3')