question

iSumit10 avatar image
iSumit10 asked

sql split one row to multiple rows based on number of columns available

Looking for optimized solution, considering source table will have thousands of records. Source Table: CREATE TABLE [dbo].[StageINV]( [InvNo] [varchar](50) NULL, [Cost1] [varchar](50) NULL, [AM1] [money] NULL, [Cost2] [varchar](50) NULL, [AM2] [money] NULL, [Cost3] [varchar](50) NULL, [AM3] [money] NULL, [Cost4] [varchar](50) NULL, [AM4] [money] NULL ) ON [PRIMARY] GO Data in source table: ![alt text][1] GO INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3435', N'12345', 4000.0000, N'23456', 600.0000, NULL, NULL, NULL, NULL) INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3467', N'54545', 2000.0000, NULL, NULL, NULL, NULL, NULL, NULL) INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3477', N'23456', 18000.0000, N'21414', 67800.0000, N'21567', 34500.0000, NULL, NULL) INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3488', N'75698', 9000.0000, N'23235', 9800.0000, N'23434', 8967.0000, N'33455', 45445.0000) INSERT [dbo].[StageINV] ([InvNo], [Cost1], [AM1], [Cost2], [AM2], [Cost3], [AM3], [Cost4], [AM4]) VALUES (N'3499', N'45678', 75.0000, NULL, NULL, NULL, NULL, NULL, NULL) Output table: CREATE TABLE [dbo].[MainInv]( [InvNo] [varchar](50) NOT NULL, [Cost#] [varchar](50) NOT NULL, [AM#] [money] NOT NULL ) ON [PRIMARY] Required Output: ![alt text][2] [1]: /storage/temp/1482-op.jpg [2]: /storage/temp/1481-op.jpg
sql2008r2
op.jpg (32.3 KiB)
op.jpg (43.8 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What are you doing with the "required output" table? Is it data (i.e. for storage and retrieval) or information (i.e. for presentation in a report or similar)? If it is data then I don't think it is wise to store the prefix of the InvNo in the same column as the rest of the InvNo. A column should do one job and do it well. As soon as you start expecting it to serve multiple purposes you're setting yourself up for years of unnecessary overhead. I won't go on about the denormalised source table because it seems to be something you've inherited, that you're trying to turn it back into something sensible.
1 Like 1 ·

1 Answer

·
Squirrel avatar image
Squirrel answered
; with cte as ( select [InvNo], [Cost] = [Cost1], [AM] = [AM1] from StageINV where [Cost1] is not null union all select [InvNo], [Cost] = [Cost2], [AM] = [AM2] from StageINV where [Cost2] is not null union all select [InvNo], [Cost] = [Cost3], [AM] = [AM3] from StageINV where [Cost3] is not null union all select [InvNo], [Cost] = [Cost4], [AM] = [AM4] from StageINV where [Cost4] is not null ) select rn = dense_rank() over(partition by InvNo order by Cost), * from cte order by InvNo
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.