Select with alternate value

Transact-SQL
1

Hi,

I have got a list of items I need to display in 2 ways according to their provider (Pr_Em_Id in the table). The one is by Pr_Em_Id alternating the provider, this means I want to list one product for provider 1, one product for provider 2, etc.
The 2nd list the same as above but the provider are randomly sorted.

I tried several ways but couldn't find a way around. Should I use a stored procedure ?

Here is the script:
`CREATE TABLE [dbo].[tblTemp](
[Pr_Id] [int] IDENTITY(1,1) NOT NULL,
[Pr_Em_Id] [int] NOT NULL,
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[Pr_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
SET IDENTITY_INSERT [dbo].[tblTemp] ON

GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (1, 5)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (2, 4)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (3, 2)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (4, 5)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (5, 1)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (6, 3)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (7, 4)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (8, 5)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (9, 5)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (10, 2)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (11, 3)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (12, 4)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (13, 1)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (14, 2)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (15, 3)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (16, 3)
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id]) VALUES (17, 4)
GO
SET IDENTITY_INSERT [dbo].[tblTemp] OFF
GO
`

2

What is your expected result?

3

What can be seen in the picture "list 1" and "list 2" (the background color is just to highlight the "blocks" )

4

Sorry not under standing the connection between the lists and the table

5

Let me explain it differently:

Here is what I want

2016-04-21_12-48-04.jpg983×465 215 KB

`/****** Object: Table [dbo].[tblTemp] Script Date: 4/21/2016 12:51:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTemp](
[Pr_Id] [int] NOT NULL,
[Pr_Em_Id] [int] NOT NULL,
[Pr_Em_Name] nvarchar NULL,
[Pr_Product] nvarchar NULL,
CONSTRAINT [PK_tblTemp] PRIMARY KEY CLUSTERED
(
[Pr_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
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (1, 5, N'John Doe
', N'Apple Type 1')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (2, 5, N'John Doe
', N'Apple Type 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (3, 5, N'John Doe
', N'Apple Type 3')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (4, 5, N'John Doe
', N'Pineapple ')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (5, 1, N'Karl Wendt
', N'Apple Red')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (6, 1, N'Karl Wendt
', N'Pineapple ')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (7, 4, N'Pablo Perez
', N'Apple Green 1')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (8, 4, N'Pablo Perez
', N'Apple Green 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (9, 4, N'Pablo Perez
', N'Apple Red')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (10, 4, N'Pablo Perez
', N'Pineapple Type 5')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (11, 3, N'Paolo Rossi
', N'Pineapple ')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (12, 3, N'Paolo Rossi
', N'Pineapple Type 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (13, 3, N'Paolo Rossi
', N'Pineapple Type 3')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (14, 3, N'Paolo Rossi
', N'Pineapple Type 4')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (15, 2, N'Paul Ué
', N'Peach Type 1')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (16, 2, N'Paul Ué
', N'Peach Type 2')
GO
INSERT [dbo].[tblTemp] ([Pr_Id], [Pr_Em_Id], [Pr_Em_Name], [Pr_Product]) VALUES (17, 2, N'Paul Ué
', N'Pineapple ')
GO
`

6

Based upon an old SP I had I came up with this. Anyway making it more simple ?

`ALTER PROCEDURE [dbo].[sp_Read]
AS
BEGIN

declare @sSQL varchar(5000)


set @sSQL = COALESCE(@sSQL + ' ','')

create table #MyHead (
Pr_Id int,
Pr_Em_Id int,
Pr_Em_Name nvarchar(50),
SortBy int,
Random int IDENTITY(1,1) )

set @sSQL = 'SELECT Pr_Id, Pr_Em_Id,Pr_Em_Name,  '
	set @sSQL = @sSQL + ' CAST(NULL AS INT) AS SortBy'
set @sSQL = @sSQL + ' FROM tblTemp'

insert into #MyHead
exec(@sSQL)

--===== Add the necessary clustered index for the "quirky" update
CREATE CLUSTERED INDEX IXU_#MyHead ON #MyHead (Pr_Em_Id,Random)

--===== Create a couple of variables to use in the "quirky" update and preset them
DECLARE @PrevSortBy INT,
@PrevPr_Em_Id INT

SELECT @PrevSortBy = 0,
@PrevPr_Em_Id= 0

--===== Do the "quirky" update. Think of it as "ROW_NUMBER() OVER" for SQL Server 2000
UPDATE #MyHead
SET @PrevSortBy = SortBy = CASE WHEN @PrevPr_Em_Id = Pr_Em_Id
THEN @PrevSortBy + 1
ELSE 1
END,
@PrevPr_Em_Id = Pr_Em_Id
FROM #MyHead WITH(INDEX(0))

--===== Produce the "semi random" output.
SELECT tblTemp.Pr_Id, tblTemp.Pr_Em_Id, tblTemp.Pr_Em_Name,Pr_Product
FROM tblTemp INNER JOIN #MyHead ON tblTemp.Pr_Id = #MyHead.Pr_Id
ORDER BY SortBy,case when left(tblTemp.Pr_Em_Name, 1) >= CHAR(65 + 26 * rand()) then 0 else 1 end
--ORDER BY SortBy,Pr_Em_Id --Semi random as requested

drop table #MyHead

END

`