Solved

add subitems into one field

Posted on 2015-02-08
7
98 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now