Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

add subitems into one field

Posted on 2015-02-08
7
Medium Priority
?
113 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

715 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