编号以逗号分隔的行中的重复名称

I am stuck in a query and have no idea what to do. I need to number every duplicate name in a comma-separated row

我陷入了疑问,不知道该怎么做。我需要在逗号分隔的行中为每个重复的名称编号

For example in attached picture, Row 7th has Midazolam two times. So in this case first name should be Midazolam(1) and second should be Midazolam(2) and so on. Is this possible using a SQL query somehow?

例如在附图中,第7行有咪达唑仑两次。所以在这种情况下,名字应该是咪达唑仑(1),第二名应该是咪达唑仑(2),依此类推。这是否可能以某种方式使用SQL查询?

Here is a query to generate dummy database and data:

这是一个生成虚拟数据库和数据的查询:

USE [master]
GO
/****** Object:  Database [DummyDataBase]    Script Date: 10/5/2015 12:42:30 PM ******/
CREATE DATABASE [DummyDataBase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DummyDataBase', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\DummyDataBase.mdf' , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DummyDataBase_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\DummyDataBase_log.ldf' , SIZE = 16576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DummyDataBase] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DummyDataBase].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [DummyDataBase] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DummyDataBase] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DummyDataBase] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DummyDataBase] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DummyDataBase] SET ARITHABORT OFF 
GO
ALTER DATABASE [DummyDataBase] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DummyDataBase] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DummyDataBase] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DummyDataBase] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DummyDataBase] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DummyDataBase] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DummyDataBase] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DummyDataBase] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DummyDataBase] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DummyDataBase] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DummyDataBase] SET  DISABLE_BROKER 
GO
ALTER DATABASE [DummyDataBase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DummyDataBase] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DummyDataBase] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [DummyDataBase] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [DummyDataBase] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DummyDataBase] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [DummyDataBase] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [DummyDataBase] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [DummyDataBase] SET  MULTI_USER 
GO
ALTER DATABASE [DummyDataBase] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DummyDataBase] SET DB_CHAINING OFF 
GO
ALTER DATABASE [DummyDataBase] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [DummyDataBase] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
USE [DummyDataBase]
GO

GO
/****** Object:  Table [dbo].[TestTable]    Script Date: 10/5/2015 12:42:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestTable](

    [DrugName] [text] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT [dbo].[TestTable] ([DrugName]) VALUES ('Midazolam, Ranitidine, Midazolam, Propofol, Cephazolin, Lignocaine, Propofol, Propofol, Fentanyl, Fentanyl, Oxygen, Isoflurane, Oxygen, Isoflurane, Oxygen, Isoflurane, Oxygen, Isoflurane, Oxygen, Nitrous Ox, Oxygen, Nitrous Ox, Isoflurane, Oxygen, Nitrous Ox, Plasmalyte, Plasmalyte, Plasmalyte, Plasmalyte, Isoflurane, Oxygen, Isoflurane')
INSERT [dbo].[TestTable] ([DrugName]) VALUES ('Midazolam, Ranitidine, Midazolam, Propofol, Cephazolin, Lignocaine, Propofol, Propofol, Fentanyl, Fentanyl, Oxygen, Isoflurane, Oxygen, Isoflurane, Oxygen, Isoflurane, Oxygen, Isoflurane, Oxygen, Nitrous Ox, Oxygen, Nitrous Ox, Isoflurane, Oxygen, Nitrous Ox, Plasmalyte, Plasmalyte, Plasmalyte, Plasmalyte, Isoflurane, Oxygen, Isoflurane')

USE [master]
GO
ALTER DATABASE [DummyDataBase] SET  READ_WRITE 
GO

1 个解决方案

#1

Ultimately, storing a list of values in a single field will create headaches. The solution, even if you don't want to store the values in a normalized fashion, is to split the listed values into separate rows. I use XML functionality to split the string into separate rows, then apply a ROW_NUMBER() and a COUNT() to change the names as desired. Finally using XML functionality again to rebuild the lists. If you want the order of the names in the lists to be preserved you'll have to add numbering for use in the ORDER BY:

最终,将值列表存储在单个字段中会产生令人头疼的问题。即使您不希望以规范化方式存储值,解决方案是将列出的值拆分为单独的行。我使用XML功能将字符串拆分为单独的行,然后应用ROW_NUMBER()和COUNT()来根据需要更改名称。最后再次使用XML功能重建列表。如果您希望保留列表中名称的顺序,则必须添加编号以便在ORDER BY中使用:

;WITH  cte AS (SELECT RTRIM(LTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS Txt,ID    
               FROM  (SELECT CAST ('<M>' + REPLACE(DrugName, ',', '</M><M>') + '</M>' AS XML) AS DATA
                            ,ID
                      FROM #test
                     ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))
      ,cte2 AS (SELECT *,CAST(ROW_NUMBER() OVER(PARTITION BY ID,Txt ORDER BY ID,Txt)AS VARCHAR(50)) AS RN
                        ,COUNT(*) OVER(PARTITION BY ID,Txt) AS Dup_CT
                FROM cte
                )
SELECT DISTINCT ID,STUFF((SELECT DISTINCT ',' +   CASE WHEN Dup_CT > 1 THEN Txt+'('+RN+')' ELSE Txt END
                                    FROM cte2 a
                                    WHERE a.ID = b.ID
                                    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') 
                                    ,1,1,'')

FROM  cte2 b

Note: I'm assuming you have an ID field along with each string, if not you'll need to add one for this solution, so it can differentiate between rows after separation.

注意:我假设您有一个ID字段以及每个字符串,如果不是,您需要为此解决方案添加一个,因此它可以在分离后区分行。

Demo: SQL Fiddle

演示:SQL小提琴