Tech
Forums
Jobs
Books
Events
Videos
Live
More
Interviews
Certification
Training
Career
Members
News
Blogs
Contribute
An Article
A Blog
A Video
An Ebook
An Interview Question
Register
Login
0
Answer
Complex PIVOT SQL Query Help needed
Technetshadow
10y
746
1
Reply
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')
Post
Reset
Cancel
Answers (
0
)
Next Recommended Forum
why its doesnt update?
sql server connection error