Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

add subitems into one field

Posted on 2015-02-08
7
Medium Priority
?
116 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
7 Comments
 
LVL 4

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

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 1000 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 1000 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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

963 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