none
Grouping or Aggregating like Items RRS feed

  • Question

  • Just diving into SQL and had seen a report format that I like and was hope this is the right forum to ask. i am familiar with basic select statements and grouping but I am stumped on how to do this or if it is possible with a single data table.

    Anyways below is a sample of a table I have.

    Table:

    Item Loc Frozen Scanned Adj
    1457481 85 0 1 1
    1457481 85 0 1 1
    1457481 25 1 1 0
    1457481 25 1 1 0
    1457481 35 1 1 0
    1457481 45 1 1 0

    Here is the format I would like to show:

    Item GoodLoc Adj
    1457481 2@25
    1@35
    1@45
    2@85
         

    Is this even possible without joining additional tables? Any assistance is greatly appreciated.
    Tuesday, October 3, 2017 8:19 PM

Answers

  • CREATE TABLE Test (
    item int,
    loc int,
    frozen int,
    scanned int,
    adj int
    )
    
    INSERT INTO Test VALUES (1457481,85,0,1,1)
    ,(1457481,85,0,1,1)
    ,(1457481,25,1,1,0)
    ,(1457481,25,1,1,0)
    ,(1457481,35,1,1,0)
    ,(1457481,45,1,1,0)
    
    WITH CTE AS (
    SELECT item, loc, 
    CAST(SUM(CASE WHEN frozen=0 THEN 0 ELSE 1 END) AS VARCHAR(10))+'@'+CAST(loc AS varchar(10)) GoodLoc,
    SUM(CASE WHEN frozen=0 THEN 0 ELSE 1 END) nbrLoc,
    CAST(SUM(CASE WHEN Adj=0 THEN 0 ELSE 1 END) AS VARCHAR(10))+'@'+CAST(loc AS varchar(10)) Adj,
    SUM(CASE WHEN Adj=0 THEN 0 ELSE 1 END) nbrAdj
    FROM Test 
    GROUP BY item, loc)
    
    SELECT t.item,
    (
    SELECT ' '+ u.GoodLoc
    FROM CTE u 
    WHERE u.item=t.item AND u.nbrLoc>0
    FOR XML PATH('')
    ) GoodLoc,
    (
    SELECT ' '+ u.Adj
    FROM CTE u 
    WHERE u.item=t.item AND u.nbrAdj>0
    FOR XML PATH('')
    ) Adj
    FROM Test t 
    GROUP BY t.item
    
    


    Ousama EL HOR, MCSE|MCSA|MCP. Please mark as answered, If you feel happy with this answer.

    Tuesday, October 3, 2017 9:19 PM
  • Try the below,Hope this may help you.

    CREATE TABLE [dbo].[test](
    [Item] [int] NULL,
    [Loc] [int] NULL,
    [Frozen] [int] NULL,
    [Scanned] [int] NULL,
    [Adj] [int] NULL
    ) ON [PRIMARY]

    select 
    item,frozen,Adj,
    ltrim(rtrim(cast(count(loc) as char(10)))) +'@' +cast(loc as char(10)) as GoodLoc ,
    ltrim(rtrim(cast(count(Adj) as char(10)))) +'@' +cast(loc as char(10)) as Adj 
    from test group by item,loc,Adj,frozen
    order by frozen

    DECLARE @One TABLE
    (
    Item int,
    frozen int,
    Adj int,
    GoodLoc char(10),
    Adj char(10)
    )

    insert into @One
    select 
    item,frozen,Adj,
    ltrim(rtrim(cast(count(loc) as char(10)))) +'@' +cast(loc as char(10)) as GoodLoc ,
    ltrim(rtrim(cast(count(Adj) as char(10)))) +'@' +cast(loc as char(10)) as Adj 
    from test group by item,loc,Adj,frozen
    order by frozen

    select item,cast(0 as char(10)),cast(adj as char(10)) from @One where frozen=0
    union all
    select item,cast(goodloc as char(10)),cast(0 as char(10)) from @One where Adj=0
    Tuesday, October 3, 2017 9:12 PM

All replies

  • Try the below,Hope this may help you.

    CREATE TABLE [dbo].[test](
    [Item] [int] NULL,
    [Loc] [int] NULL,
    [Frozen] [int] NULL,
    [Scanned] [int] NULL,
    [Adj] [int] NULL
    ) ON [PRIMARY]

    select 
    item,frozen,Adj,
    ltrim(rtrim(cast(count(loc) as char(10)))) +'@' +cast(loc as char(10)) as GoodLoc ,
    ltrim(rtrim(cast(count(Adj) as char(10)))) +'@' +cast(loc as char(10)) as Adj 
    from test group by item,loc,Adj,frozen
    order by frozen

    DECLARE @One TABLE
    (
    Item int,
    frozen int,
    Adj int,
    GoodLoc char(10),
    Adj char(10)
    )

    insert into @One
    select 
    item,frozen,Adj,
    ltrim(rtrim(cast(count(loc) as char(10)))) +'@' +cast(loc as char(10)) as GoodLoc ,
    ltrim(rtrim(cast(count(Adj) as char(10)))) +'@' +cast(loc as char(10)) as Adj 
    from test group by item,loc,Adj,frozen
    order by frozen

    select item,cast(0 as char(10)),cast(adj as char(10)) from @One where frozen=0
    union all
    select item,cast(goodloc as char(10)),cast(0 as char(10)) from @One where Adj=0
    Tuesday, October 3, 2017 9:12 PM
  • CREATE TABLE Test (
    item int,
    loc int,
    frozen int,
    scanned int,
    adj int
    )
    
    INSERT INTO Test VALUES (1457481,85,0,1,1)
    ,(1457481,85,0,1,1)
    ,(1457481,25,1,1,0)
    ,(1457481,25,1,1,0)
    ,(1457481,35,1,1,0)
    ,(1457481,45,1,1,0)
    
    WITH CTE AS (
    SELECT item, loc, 
    CAST(SUM(CASE WHEN frozen=0 THEN 0 ELSE 1 END) AS VARCHAR(10))+'@'+CAST(loc AS varchar(10)) GoodLoc,
    SUM(CASE WHEN frozen=0 THEN 0 ELSE 1 END) nbrLoc,
    CAST(SUM(CASE WHEN Adj=0 THEN 0 ELSE 1 END) AS VARCHAR(10))+'@'+CAST(loc AS varchar(10)) Adj,
    SUM(CASE WHEN Adj=0 THEN 0 ELSE 1 END) nbrAdj
    FROM Test 
    GROUP BY item, loc)
    
    SELECT t.item,
    (
    SELECT ' '+ u.GoodLoc
    FROM CTE u 
    WHERE u.item=t.item AND u.nbrLoc>0
    FOR XML PATH('')
    ) GoodLoc,
    (
    SELECT ' '+ u.Adj
    FROM CTE u 
    WHERE u.item=t.item AND u.nbrAdj>0
    FOR XML PATH('')
    ) Adj
    FROM Test t 
    GROUP BY t.item
    
    


    Ousama EL HOR, MCSE|MCSA|MCP. Please mark as answered, If you feel happy with this answer.

    Tuesday, October 3, 2017 9:19 PM
  • There is an error I am getting while running your code through SQL Server, but the format you are showing is good and might be what I need.

    Msg 319, Level 15, State 1, Line 16
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Tuesday, October 3, 2017 10:14 PM
  • Thank you so much! I just have to worry about formats in my report, but this is pretty much what i was looking for.

    Cheers!

    Tuesday, October 3, 2017 10:17 PM
  • Got it. Thank you!
    Wednesday, October 4, 2017 3:42 AM