multi value in same record sql

Question

I have 4 tables :

The main table Applications with sample data like this:

Id | Craft_Name | Area     | PPE       | Potential
---+------------+----------+-----------+-------------
 1 | anas       | 1, 2, 3, | 4, 1, 3,  | 4, 2,

Second table Area:

Id | Area
---+----------
 1 | Jordan
 2 | USA
 3 | China

third table PPE

Id | PPE 
---+-----------------
 1 | Safety Shoes   
 2 | Safety Gloves  
 3 | Electrical Gloves
 4 | Thermal Gloves

Fourth table Potential:

ID | Potential
---+----------------------
 1 | Working at Height
 2 | Falling Objects
 3 | Sharp Edges
 4 | Inhalation

Finally I need the result of SQL query as follows:

Id | Craft_Name | Area                | PPE                                                   | Potential
---+------------+---------------------+-------------------------------------------------------+-----------------------------
 1 | anas       | Jordan, USA, China, | Thermal Gloves, Working at Height, Electrical Gloves, | Inhalation, Falling Objects,

Revise your design and don't use such lists. That's just making things complicated. Use linking tables. — sticky bit, Dec 23 '18 at 12:17
Hi. This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. When you do have a non-duplicate code question to post please read & act on [mcve]. PS [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/3404097) — philipxy, Dec 23 '18 at 12:39
Possible duplicate of [Convert Comma Separated column value to rows](https://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows) — philipxy, Dec 23 '18 at 12:43

score 1 · Accepted Answer · answered Dec 23 '18 at 15:57

you can do this in 2 ways.

to change your applications table structure to include new 3 many to many relation tables. (recommended and its easier to do any type on analysis)

or you can use the following code which is so complicated and combining many complex queries in it.

IF OBJECT_ID('tempdb..#AreaTable') IS NOT NULL
    DROP TABLE #AreaTable
IF OBJECT_ID('tempdb..#PPETable') IS NOT NULL
    DROP TABLE #PPETable

; WITH AreaTable AS (
    SELECT 
        A.ID, A.CreaftName, AreaName
    FROM  
        (
            SELECT T1.ID, T1.CreaftName,T1.[AreaIDs],T2.my_Splits
            FROM
                (
                    SELECT *,
                        CAST('<X>'+replace([AreaIDs],',','</X><X>')+'</X>' as XML) as my_Xml 
                    FROM [dbo].[Applications]) T1
                    CROSS APPLY
                    (
                        SELECT 
                            my_Data.D.value('.','varchar(50)') as my_Splits
                        FROM 
                            T1.my_Xml.nodes('X') as my_Data(D)
                    ) T2
                ) A 
    INNER JOIN [dbo].[Areas] B on A.my_splits = B.ID
)

--SELECT * FROM TempTable

SELECT DISTINCT ST2.ID, St2.CreaftName,
    SUBSTRING(
        (
            SELECT ','+ ST1.AreaName  AS [text()]
            FROM AreaTable ST1
            WHERE ST1.ID = ST2.ID
            ORDER BY ST1.ID
            FOR XML PATH ('')
        ), 2, 1000) [Areas]
INTO #AreaTable
FROM [dbo].[Applications] ST2

;WITH PPETable AS (
    SELECT 
        A.ID, A.CreaftName, PPEName
    FROM  
        (
            SELECT T1.ID, T1.CreaftName,T1.PPEIDs,T2.my_Splits
            FROM
                (
                    SELECT *,
                        CAST('<X>'+replace(PPEIDs,',','</X><X>')+'</X>' as XML) as my_Xml 
                    FROM [dbo].[Applications]) T1
                    CROSS APPLY
                    (
                        SELECT 
                            my_Data.D.value('.','varchar(50)') as my_Splits
                        FROM 
                            T1.my_Xml.nodes('X') as my_Data(D)
                    ) T2
                ) A 
    INNER JOIN [dbo].[PPE] B on A.my_splits = B.ID
)

--SELECT * FROM TempTable

SELECT DISTINCT ST2.ID, St2.CreaftName,
    SUBSTRING(
        (
            SELECT ','+ ST1.PPEName  AS [text()]
            FROM PPETable ST1
            WHERE ST1.ID = ST2.ID
            ORDER BY ST1.ID
            FOR XML PATH ('')
        ), 2, 1000) [PPEs]
INTO #PPETable
FROM [dbo].[Applications] ST2

SELECT MainTable.ID, MainTable.CreaftName, A.Areas, B.[PPEs]
FROM 
    [dbo].[Applications] AS MainTable
    LEFT OUTER JOIN #AreaTable AS A ON MainTable.ID = A.ID
    LEFT OUTER JOIN #PPETable AS B ON MainTable.ID = B.ID


IF OBJECT_ID('tempdb..#AreaTable') IS NOT NULL
    DROP TABLE #AreaTable
IF OBJECT_ID('tempdb..#PPETable') IS NOT NULL
    DROP TABLE #PPETable

and you can use the following code to generate the tables i did create this script with for your reference

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PPE]') AND type in (N'U'))
    DROP TABLE [dbo].[PPE]
    GO
    /****** Object:  Table [dbo].[Areas]    Script Date: 23/12/2018 10:43:40 AM ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Areas]') AND type in (N'U'))
    DROP TABLE [dbo].[Areas]
    GO
    /****** Object:  Table [dbo].[Applications]    Script Date: 23/12/2018 10:43:40 AM ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applications]') AND type in (N'U'))
    DROP TABLE [dbo].[Applications]
    GO
    /****** Object:  Table [dbo].[Applications]    Script Date: 23/12/2018 10:43:40 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Applications]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Applications](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [CreaftName] [nvarchar](50) NULL,
        [AreaIDs] [nvarchar](50) NULL,
        [PPEIDs] [nvarchar](50) NULL,
        CONSTRAINT [PK_Applications] 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]
    END
    GO
    /****** Object:  Table [dbo].[Areas]    Script Date: 23/12/2018 10:43:40 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Areas]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Areas](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [AreaName] [nvarchar](50) NULL,
        CONSTRAINT [PK_Ares] 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]
    END
    GO
    /****** Object:  Table [dbo].[PPE]    Script Date: 23/12/2018 10:43:40 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PPE]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[PPE](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [PPEName] [nvarchar](50) NULL,
        CONSTRAINT [PK_PPE] 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]
    END
    GO
    SET IDENTITY_INSERT [dbo].[Applications] ON 

    INSERT [dbo].[Applications] ([ID], [CreaftName], [AreaIDs], [PPEIDs]) VALUES (1, N'Anas', N'1,3,2,', N'1,3')
    INSERT [dbo].[Applications] ([ID], [CreaftName], [AreaIDs], [PPEIDs]) VALUES (2, N'Islam', N',6,5,3', N'2')
    INSERT [dbo].[Applications] ([ID], [CreaftName], [AreaIDs], [PPEIDs]) VALUES (3, N'Mohammad', N'4', NULL)
    SET IDENTITY_INSERT [dbo].[Applications] OFF
    SET IDENTITY_INSERT [dbo].[Areas] ON 

    INSERT [dbo].[Areas] ([ID], [AreaName]) VALUES (1, N'Jordan')
    INSERT [dbo].[Areas] ([ID], [AreaName]) VALUES (2, N'China')
    INSERT [dbo].[Areas] ([ID], [AreaName]) VALUES (3, N'USA')
    INSERT [dbo].[Areas] ([ID], [AreaName]) VALUES (4, N'Colombia')
    INSERT [dbo].[Areas] ([ID], [AreaName]) VALUES (5, N'Costa Rica')
    INSERT [dbo].[Areas] ([ID], [AreaName]) VALUES (6, N'Panama')
    SET IDENTITY_INSERT [dbo].[Areas] OFF
    SET IDENTITY_INSERT [dbo].[PPE] ON 

    INSERT [dbo].[PPE] ([ID], [PPEName]) VALUES (1, N'Safety Shoes')
    INSERT [dbo].[PPE] ([ID], [PPEName]) VALUES (2, N'Safety Gloves')
    INSERT [dbo].[PPE] ([ID], [PPEName]) VALUES (3, N'Electrical Gloves')
    SET IDENTITY_INSERT [dbo].[PPE] OFF

score 0 · Answer 2 · edited Dec 23 '18 at 15:36

0

For example - Area table (continue the other tables)

select 
    (select Area.Area + ',' 
     from dbo.Area 
     where charindex(',' + cast(Area.Id as varchar(10)) + ',', ',' + isnull(Applications.Area, '') + ',') > 0 
     for xml path('')) 
from 
    dbo.Applications

edited Dec 23 '18 at 15:36

marc_s

732,580
175
1,330
1,459

answered Dec 23 '18 at 12:19

inon

1,689
1
19
34

Right. continue that way to the other fields – inon Dec 23 '18 at 14:17

multi value in same record sql

2 Answers2