Solved

add subitems into one field

Posted on 2015-02-08
7
112 Views
Last Modified: 2015-02-12
i have a list
groupid varchar(12)


in a second list are the details from the first table
like
A1,L
A1,M
A1,XL
i want to add each detail into one Field so that i have the details together in one field
what i need is a row with content
A1,L/M/XL

im looking for a smart way to do this
0
Comment
Question by:ebertsys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:Shane Kahkola
ID: 40597304
Help us understand the fields and types in both tables, please.  It's hard to know how to tell you to combine them (if you should at all) if we don't know the data structures.  It looks like you want it all to go into a varchar(12) field, but what are the table structures?
0
 

Author Comment

by:ebertsys
ID: 40597311
Main table
groupid varchar(12)
sizes varchar (100)
sample :
A1 , [the sizes from the second table in one field] LMXL
detail table
groupid varchar(12)
Size varchar(4)
sample:
A1,L
A1,M
A1,XL
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40597313
Table1
-------------
Type
----
A1
A2
A3
.
.

Table2
----------
Type    Value
-------    --------
A1           L
A1           M
A1           XL
A2           L
A2           M
A2           XL
A2          XXL   <-- do some type have a different value?

Is the structure of your table like this? Cahnge table and column names to whatever you have.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:ebertsys
ID: 40597316
yes...and i need all sizes into one field from table1
0
 
LVL 3

Expert Comment

by:Shane Kahkola
ID: 40597318
I don't have the data to play with, but you might try using the STUFF() command.

https://msdn.microsoft.com/en-us/library/ms188043.aspx
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40597334
You'll have to explain your source data schema a little better, but this test T-SQL using XML Path and STUFF returns the output that you want
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp
GO

CREATE TABLE #tmp (group_id varchar(12), size varchar(5))

INSERT INTO #tmp (group_id, size) 
VALUES 
   ('A1', 'L') ,('A1', 'M'), ('A1', 'S'),
   ('A2', 'XL') ,('A2', 'M'), ('A2', 'S'),
   ('B1', 'L') ,('B1', 'M'), ('B1', 'S')

SELECT group_id + ', ' + LEFT(details, LEN(details) -1) as details
FROM ( 
   SELECT DISTINCT t1.group_id,
   stuff((
      SELECT size + '/'
      FROM #tmp t2 
      WHERE t1.group_id = t2.group_id
      FOR XML PATH('')), 1, 0, '') as details
      FROM #tmp t1) a

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40597609
As a select query:
SELECT
      main.groupid
    , SIZECONCAT
FROM main
      CROSS APPLY (
            SELECT
                  STUFF((
                        SELECT
                              '/' + size
                        FROM detail
                        WHERE main.groupid = detail.groupid
                        ORDER BY size
                        FOR xml PATH ('')
                  ), 1, 1, '')
      ) AS CA (SIZECONCAT)
;

Open in new window


But given the presence of main.sizes it seems you want to store this concatenation (not sure why), so, as an update query:
UPDATE main
SET sizes = SIZECONCAT
FROM main m
      CROSS APPLY (
            SELECT
                  STUFF((
                        SELECT
                              '/' + size
                        FROM dbo.[detail]
                        WHERE m.groupid = detail.groupid
                        ORDER BY size
                        FOR xml PATH ('')
                  ), 1, 1, '')
      ) AS CA (SIZECONCAT)
;


select
*
from main

Open in new window


see: http://sqlfiddle.com/#!3/4732a/1

details:
**MS SQL Server 2008 Schema Setup**:

    
    CREATE TABLE Main
    	([groupid] varchar(12), [sizes] varchar(100))
    ;
    	
    INSERT INTO Main
    	([groupid])
    VALUES
    	('A1')
      , ('A2')
    ;
    
    
    
    CREATE TABLE detail
    	([groupid] varchar(12), [Size] varchar(4))
    ;
    	
    INSERT INTO detail
    	([groupid], [Size])
    VALUES
    	('A1', 'L'),
    	('A1', 'M'),
    	('A1', 'XL')
    ;

**Query 1**:

    SELECT
          main.groupid
        , SIZECONCAT
    FROM main
          CROSS APPLY (
                SELECT
                      STUFF((
                            SELECT
                                  '/' + size
                            FROM detail
                            WHERE main.groupid = detail.groupid
                            ORDER BY size
                            FOR xml PATH ('')
                      ), 1, 1, '')
          ) AS CA (SIZECONCAT)
    

**[Results][2]**:
    
    | GROUPID | SIZECONCAT |
    |---------|------------|
    |      A1 |     L/M/XL |
    |      A2 |     (null) |


**Query 2**:

    update main
    set sizes = SIZECONCAT
    FROM main m
          CROSS APPLY (
                SELECT
                      STUFF((
                            SELECT
                                  '/' + size
                            FROM dbo.[detail]
                            WHERE m.groupid = detail.groupid
                            ORDER BY size
                            FOR xml PATH ('')
                      ), 1, 1, '')
          ) AS CA (SIZECONCAT)
    

**[Results][3]**:
    

**Query 3**:

    select
    *
    from main
    

**[Results][4]**:
    
    | GROUPID |  SIZES |
    |---------|--------|
    |      A1 | L/M/XL |
    |      A2 | (null) |



  [1]: http://sqlfiddle.com/#!3/4732a/1

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question