Query help

Question

Query help

vsamantha35

SSChampion

Points: 11529
More actions
November 10, 2017 at 11:17 pm

#324417

Hi All,
Need some help in tsql. This is my table. I have put some sample data and expecting output as shown in the below screenshot .
For every new start of 1,2 a new txnid should be generated which means those 2 rows belong to one transaction.
Please help.
CREATE TABLE [dbo].[test](
[id] [int] NULL,
[c1] [varchar](100)
)
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'zsdssfd')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'yosmd')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'flodC')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'ioppsps')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'dvev')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'vedmdm')
GO
select * from test;
Expected output:
Thanks,
Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 1

vsamantha35 - Friday, November 10, 2017 11:17 PM
Hi All,
Need some help in tsql. This is my table. I have put some sample data and expecting output as shown in the below screenshot .
For every new start of 1,2 a new txnid should be generated which means those 2 rows belong to one transaction.
Please help.
CREATE TABLE [dbo].[test](
[id] [int] NULL,
[c1] [varchar](100)
)
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'zsdssfd')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'yosmd')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'flodC')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'ioppsps')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (1, N'dvev')
GO
INSERT [dbo].[test] ([id], [c1]) VALUES (2, N'vedmdm')
GO
select * from test;
Expected output:
Thanks,
Sam

You can't... At least not based on what you have here...

Tables, don't have an intrinsic order of their own, which means you'll have to have something that either holds the insertion order (an identity column or inserted date) or something to group the pairs (the thing you're trying to create).

That said... DENSE_RANK() will do what you're asking... but, without anything to sort on, you won't be able to control which groups are created.

-- with an identy column... SELECT txnid = DENSE_RANK() OVER (PARTITION BY t.id ORDER BY t.ident_col), t.id, t.c1 FROM dbo.test t ORDER BY t.ident_col;

Results...

txid id c1 ----- ---- -------- 1 1 zsdssfd 1 2 yosmd 2 1 flodC 2 2 ioppsps 3 1 dvev 3 2 vedmdm

-- without anything... WITH cte_add_rownum AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), t.id, t.c1 FROM dbo.test t ) SELECT txnid = DENSE_RANK() OVER (PARTITION BY ar.id ORDER BY ar.rn), ar.id, ar.c1 FROM cte_add_rownum ar;

Results

txnid id c1
------ ----- --------
1  1 zsdssfd
2  1 flodC
3  1 dvev
1  2 yosmd
2  2 ioppsps
3  2 vedmdm