locked
How to create a list of Item Locations from a list of Locations RRS feed

  • Question

  • I have a table something like this:

    Location  Item       Count
    --------- ---------- ------
    19        050-05     1
    20        050-05     1
    3         050-19     72
    12        050-19     72
    77        050-19     56
    90        050-19     24
    7         050-21     96
    13        050-21     64
    24        050-21     96
    9         050-27     96
    15        050-27     96
    116       050-27     64
    156       050-27     96
    184       050-27     64
    57        056-05     1
    71        085-07     90
    88        085-07     90
    135       085-07     18

    I am having trouble coming up with the SQL to convert it to something like this:

    Item     Total Locations
    -------- ----- ------------
    050-05   2     19, 20
    050-19   224   3, 12, 77, 90
    050-21   256   7, 13, 24
    050-27   416   9, 15, 116, 156, 184
    056-05   1     57
    085-07   198   88, 135

    Friday, June 21, 2013 6:36 PM

Answers

  • Create Table #Foo(Location int, Item varchar(20), Count int);
    Insert #Foo(Location, Item, Count) Values
    (19,        '050-05',     1),
    (20,        '050-05',     1),
    (3,         '050-19',     72),
    (12,        '050-19',     72),
    (77,        '050-19',     56),
    (90,        '050-19',     24),
    (7,         '050-21',     96),
    (13,        '050-21',     64),
    (24,        '050-21',     96),
    (9,         '050-27',     96),
    (15,        '050-27',    96),
    (116,       '050-27',     64),
    (156,       '050-27',     96),
    (184,       '050-27',     64),
    (57,        '056-05',     1),
    (71,        '085-07',     90),
    (88,        '085-07',     90),
    (135,       '085-07',     18);
    
    Select Distinct f.Item,
      Sum(f.Count) Over(Partition By f.Item) As Total,
      Stuff((Select ', ' + Cast(Location As varchar(11))
         From #Foo f2 Where f.Item = f2.Item Order By f2.Location
    	   For XML Path ('') ), 1, 2, '')
    From #Foo f
    Order By f.Item;
    go
    Drop Table #Foo;

    Tom
    • Marked as answer by JCauthon Friday, June 21, 2013 7:31 PM
    Friday, June 21, 2013 7:02 PM

All replies

  • Create Table #Foo(Location int, Item varchar(20), Count int);
    Insert #Foo(Location, Item, Count) Values
    (19,        '050-05',     1),
    (20,        '050-05',     1),
    (3,         '050-19',     72),
    (12,        '050-19',     72),
    (77,        '050-19',     56),
    (90,        '050-19',     24),
    (7,         '050-21',     96),
    (13,        '050-21',     64),
    (24,        '050-21',     96),
    (9,         '050-27',     96),
    (15,        '050-27',    96),
    (116,       '050-27',     64),
    (156,       '050-27',     96),
    (184,       '050-27',     64),
    (57,        '056-05',     1),
    (71,        '085-07',     90),
    (88,        '085-07',     90),
    (135,       '085-07',     18);
    
    Select Distinct f.Item,
      Sum(f.Count) Over(Partition By f.Item) As Total,
      Stuff((Select ', ' + Cast(Location As varchar(11))
         From #Foo f2 Where f.Item = f2.Item Order By f2.Location
    	   For XML Path ('') ), 1, 2, '')
    From #Foo f
    Order By f.Item;
    go
    Drop Table #Foo;

    Tom
    • Marked as answer by JCauthon Friday, June 21, 2013 7:31 PM
    Friday, June 21, 2013 7:02 PM
  • Where is the DDL? This is minimal polite behavior on any SQL forum. 

    Why did you request the destruction of First Normal Form (1NF)? This is minimal RDBMS education. 

    Please, please, please read just one book on RDBMS and SQL. Reading postings like this is painful. I feel like geography teacher who is seeing "flat Earthers" in his class. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 21, 2013 7:18 PM
  • Perfect!!  Thanks, I have never used the STUFF statement before...
    Friday, June 21, 2013 7:31 PM