Solved

add subitems into one field

Posted on 2015-02-08
7
105 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 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 33

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 65

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loop to go backward 90 days 2 18
export sql results to csv 6 34
Sql Server group by 10 26
T-SQL:  Collapsing 9 22
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

786 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