Sql Query Result

Question

Hi This is nagendra i need a help in Sql Server 2005





i have two tables

USE [NewMedia]
GO
/****** Object:  Table [dbo].[tbl_Mas_Break]    Script Date: 11/07/2009 07:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Mas_Break](
 [BreakId] [bigint] IDENTITY(1,1) NOT NULL,
 [BreakName] [nvarchar](150) NOT NULL,
 [BreakStartTime] [nvarchar](50) NOT NULL,
 [BreakDuration] [nvarchar](50) NOT NULL,
 [BreakRuleUId] [nvarchar](150) NOT NULL,
 [BookedSeconds] [int] NULL,
 [RemainingSeconds] [int] NULL,
 [CreatedOn] [datetime] NOT NULL,
 [CreatedBy] [nvarchar](150) NOT NULL,
 [LastEditOn] [datetime] NULL,
 [LastEditBy] [nvarchar](150) NULL,
 [Status] [nvarchar](4) NOT NULL CONSTRAINT [DF_tbl_Mas_Break_Status]  DEFAULT (N'A'),
 [Privilege] [bit] NOT NULL CONSTRAINT [DF_tbl_Mas_Break_Privilege]  DEFAULT ((0)),
 [BreakUId] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_tbl_Mas_Break] PRIMARY KEY CLUSTERED
(
 [BreakId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [Uni_BreakUId] UNIQUE NONCLUSTERED
(
 [BreakUId] 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
ALTER TABLE [dbo].[tbl_Mas_Break]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Mas_Break_tbl_Mas_BreakRule_BreakRuleUId] FOREIGN KEY([BreakRuleUId])
REFERENCES [dbo].[tbl_Mas_BreakRule] ([BreakRuleUId])
GO
ALTER TABLE [dbo].[tbl_Mas_Break] CHECK CONSTRAINT [FK_tbl_Mas_Break_tbl_Mas_BreakRule_BreakRuleUId]


and 2nd One:

USE [NewMedia]
GO
/****** Object:  Table [dbo].[tbl_Mas_AdBreak]    Script Date: 11/07/2009 07:41:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Mas_AdBreak](
 [AdBreakId] [bigint] IDENTITY(1,1) NOT NULL,
 [PartName] [nvarchar](50) NOT NULL,
 [AdDuration] [nvarchar](50) NOT NULL,
 [AdTypeUId] [nvarchar](50) NOT NULL,
 [BreakUId] [nvarchar](50) NOT NULL,
 [Status] [nvarchar](4) NOT NULL CONSTRAINT [DF_tbl_Mas_AdBreak_Status]  DEFAULT (N'A'),
 [Privilege] [bit] NOT NULL CONSTRAINT [DF_tbl_Mas_AdBreak_Privilege]  DEFAULT ((0)),
 [AdBreakUId] [nvarchar](50) NOT NULL,
 [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_tbl_Mas_AdBreak] PRIMARY KEY CLUSTERED
(
 [AdBreakId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [Uni_AdBreakUId] UNIQUE NONCLUSTERED
(
 [AdBreakUId] 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
ALTER TABLE [dbo].[tbl_Mas_AdBreak]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Mas_AdBreak_tbl_Mas_AdType_AdTypeUId] FOREIGN KEY([AdTypeUId])
REFERENCES [dbo].[tbl_Mas_AdType] ([AdTypeUId])
GO
ALTER TABLE [dbo].[tbl_Mas_AdBreak] CHECK CONSTRAINT [FK_tbl_Mas_AdBreak_tbl_Mas_AdType_AdTypeUId]
GO
ALTER TABLE [dbo].[tbl_Mas_AdBreak]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Mas_AdBreak_tbl_Mas_Break_BreakUId] FOREIGN KEY([BreakUId])
REFERENCES [dbo].[tbl_Mas_Break] ([BreakUId])
GO
ALTER TABLE [dbo].[tbl_Mas_AdBreak] CHECK CONSTRAINT [FK_tbl_Mas_AdBreak_tbl_Mas_Break_BreakUId]


 This is my Query :
SELECT BREAkNAME , PARTNAME FROM tbl_Mas_Break Inner Join tbl_Mas_AdBreak On tbl_Mas_AdBreak.BreakUId = tbl_Mas_Break.BreakUId WHERE BREAKRULEUID = 'BRKR1'

THIS

IS RESULT BY EXECUTING THE ABOVE QUERY

BREAKNAME PARTNAME  -----> Column Names

 

BREAK ONE Part One

 

BREAK ONE Part Two

 

BREAK TWO 1st Part

 

BREAK TWO 2nd Part

---------------------------------- 

I NEED ACTUAL RESULT

BREAKNAME PARTNAME -----> Column Names       

 

BREAK ONE   Part One

                  Part Two

 

BREAK TWO 1st Part

                  2nd Part


Plz any one can help me............
Thanks in advance.............

Answer 1

TRY

SELECT BREAkNAME , PARTNAME
FROM tbl_Mas_Break
Inner Join tbl_Mas_AdBreak On tbl_Mas_AdBreak . BreakUId = tbl_Mas_Break . BreakUId
WHERE BREAKRULEUID = 'BRKR1'
GROUP BY BREAkNAME

OR

;with cte as(
SELECT BREAkNAME , PARTNAME,
ROW_NUMBER() OVER(PARTITION BY BreakName ORDER BY PARTNAME, DESC) AS 'RowNumber',
FROM tbl_Mas_Break
Inner Join tbl_Mas_AdBreak On tbl_Mas_AdBreak . BreakUId = tbl_Mas_Break . BreakUId
WHERE BREAKRULEUID = 'BRKR1'
)
SELECT CASE WHEN RowNumber  =1 Then BreakName else NULL AS BreakName, PartName
FROM CTE
ORDER BY BreakName, RowNumber;

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

Answer 2

Hi


Thank you Melissa Suciadi u r answer is more helpful to me 




Regards,
SBSN