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
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.
; 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
16 People are following this question.
