Author |
Topic |
shanmugaraj
Posting Yak Master
219 Posts |
Posted - 2014-02-25 : 03:53:36
|
I need to sort based on the values sent from the input xml column resultdata this is an value which i have in my database, sent from front end. currently the dispaly is based on database storage level of master data. i will not be able to change the master data ordering. based on selection, i have to sort since i have an logic which stored the data into resultdata columnCREATE TABLE [dbo].[Table_Prod]( [ProdCode] [nchar](5) NULL, [ProductName] [nchar](50) NULL) ON [PRIMARY]GOINSERT [dbo].[Table_Prod] ([ProdCode], [ProductName]) VALUES (N'RD ', N'Bus ')INSERT [dbo].[Table_Prod] ([ProdCode], [ProductName]) VALUES (N'SA ', N'Ship ')INSERT [dbo].[Table_Prod] ([ProdCode], [ProductName]) VALUES (N'TR ', N'Train ')INSERT [dbo].[Table_Prod] ([ProdCode], [ProductName]) VALUES (N'AI ', N'Plane ')CREATE TABLE [dbo].[Table_detail]( [Id] [int] NOT NULL, [SalesMonth] [nchar](10) NULL, [Season] [nchar](10) NULL, [DeptData] [nchar](50) NULL, [ProdData] [nchar](50) NULL, [Description] [nchar](120) NULL, CONSTRAINT [PK_Table_detail] PRIMARY KEY CLUSTERED ( [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]GOINSERT [dbo].[Table_detail] ([Id], [SalesMonth], [Season], [DeptData], [ProdData], [Description]) VALUES (1, N'Jan ', N'Winter ', N'RO,HO ', N'RD,AI,SA ', N'Sales for the month of Jan ')INSERT [dbo].[Table_detail] ([Id], [SalesMonth], [Season], [DeptData], [ProdData], [Description]) VALUES (2, N'May ', N'Summer ', N'SO,HO ', N'AI,RD,TR,SA ', N'May sales information ')INSERT [dbo].[Table_detail] ([Id], [SalesMonth], [Season], [DeptData], [ProdData], [Description]) VALUES (3, N'Sep ', N'Rain ', N'HO,RO,SO ', N'SA,TR,RD ', N'Sales for the month of Sep in the rain season ')CREATE TABLE [dbo].[Table_Dept]( [DeptCode] [nchar](10) NULL, [DeptName] [nvarchar](50) NULL, [DeptDesc] [nvarchar](250) NULL) ON [PRIMARY]GOINSERT [dbo].[Table_Dept] ([DeptCode], [DeptName], [DeptDesc]) VALUES (N'HO ', N'HeadOffice', N'Head Office desc')INSERT [dbo].[Table_Dept] ([DeptCode], [DeptName], [DeptDesc]) VALUES (N'SO ', N'SalesOffice', N'Sales Office desc')INSERT [dbo].[Table_Dept] ([DeptCode], [DeptName], [DeptDesc]) VALUES (N'RO ', N'RegionalOffice', N'RegionalOffice desc')--========--QUERY--======== Select Id, SalesMonth, Season, DeptData, RTRIM(LTRIM(STUFF((SELECT ',' + RTRIM(LTRIM(DeptName)) FROM Table_Dept WHERE ',' + t.DeptData + ',' LIKE '%,' + RTRIM(LTRIM(deptCode)) + ',%' FOR XML PATH('')),1,1,''))) AS DeptInfo , ProdData , RTRIM(LTRIM(STUFF((SELECT ',' + RTRIM(LTRIM(ProductName)) FROM Table_Prod WHERE ',' + t.ProdData + ',' LIKE '%,' + RTRIM(LTRIM(ProdCode)) + ',%' FOR XML PATH('')),1,1,''))) AS ProductInfo , Description FROM ( SELECT Id, SalesMonth, Season, DeptData, ProdData, Description FROM dbo.Table_detail ) t /* Expected Result Current Result Id SalesMonth Season DeptData ProductInfo ProdData ProductInfo Description1 Jan Winter RO,HO HeadOffice,RegionalOffice RD,AI,SA Bus,Ship,Plane Sales for the month of Jan2 May Summer SO,HO HeadOffice,SalesOffice AI,RD,TR,SA Bus,Ship,Train,Plane May sales information3 Sep Rain HO,RO,SO HeadOffice,SalesOffice,RegionalOffice SA,TR,RD Bus,Ship,Train Sales for the month of Sep in the rain season Expected Result Id SalesMonth Season DeptData ProductInfo ProdData ProductInfo Description1 Jan Winter RO,HO RegionalOffice, HeadOffice RD,AI,SA Bus,Plane,Ship Sales for the month of Jan2 May Summer SO,HO SalesOffice, HeadOffice AI,RD,TR,SA Plane,Bus,Train,Ship May sales information3 Sep Rain HO,RO,SO HeadOffice,RegionalOffice,SalesOffice SA,TR,RD Ship,Train,Bus Sales for the month of Sep in the rain season*/ |
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2014-03-10 : 06:38:53
|
So, its just that if HeadOffice appears, you want that to come first?Try replacing the DeptInfo with this:RTRIM(LTRIM(STUFF((SELECT ',' + DeptName FROM (SELECT ROW_NUMBER() OVER (ORDER BY RTRIM(LTRIM(DeptName)) ASC ) Sort , RTRIM(LTRIM(DeptName)) DeptCode, DeptName FROM Table_Dept ) Table_Dept WHERE ',' + t.DeptData + ',' LIKE '%,' + deptCode + ',%' FOR XML PATH('')),1,1,''))) AS DeptInfo |
|
|
|
|
|