CODEWITHSUNDEEP
sqlsql-server
JohnDoe4136
JohnDoe4136

Reputation: 539

SQL group records with same value, and place that into a table

I want to group same record with SQL, the following is my result

Name  Code   Qty
data1  AG   12
data1  AS   15
data2  MS   10
data2  IS   11

I want it to be like this instead.

Name  Code   Qty Code  Qty
data1  AG    12   AS   15
data2  MS    10   IS   11

Can this be done in SQL only?

Upvotes: 0

Views: 1705

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Can this be done in SQL only?

With a variable number of columns you have to build a the query dynamically. I would hardly call this "SQL only" but it can be done in T-SQL and here is one way.

-- Sample table
declare @T table
(
  Name varchar(5),
  Code varchar(2),
  Qty int
)

-- Sample data
insert into @T values
('data1',  'AG',   12),
('data1',  'AS',   15),
('data1',  'AQ',   17),
('data2',  'MS',   10),
('data2',  'IS',   11)

declare @XML xml
declare @SQL nvarchar(max)
declare @Max int

-- Max number of codes per name
select @Max = max(C)
from (select count(*) as C
      from @T
      group by Name) as T

-- Convert table to XML
set @XML = (select Name,
                   (select Code,
                           Qty
                    from @T as T2
                    where T1.Name = T2.Name
                    for xml path('c'), type)
            from @T as T1
            group by Name
            for xml path('r'))

-- Build a dynamic query
;with Numbers(Number) as 
(
  select 1
  union all
  select Number + 1
  from Numbers
  where Number < @Max
)
select @SQL = 'select T.N.value(''Name[1]'', ''varchar(5)'') as Name ' +
               (select ',T.N.value(''c['+cast(Number as nvarchar(10))+']/Code[1]'', ''char(2)'') as Code
                        ,T.N.value(''c['+cast(Number as nvarchar(10))+']/Qty[1]'', ''int'') as Qty'
                from Numbers
                for xml path(''), type).value('.', 'nvarchar(max)') +
              ' from @xml.nodes(''/r'') as T(N)'

-- Execute query
exec sp_executesql @SQL, N'@xml xml', @XML

Result:

Name  Code Qty         Code Qty         Code Qty
----- ---- ----------- ---- ----------- ---- -----------
data1 AG   12          AS   15          AQ   17
data2 MS   10          IS   11          NULL NULL

Try here: https://data.stackexchange.com/stackoverflow/q/122860/

Upvotes: 3

user359040
user359040

Reputation:

Assuming you are using SQLServer, you could use rank to assign a sequential number to each code with the name group, like so:

select Name, Code, Qty, Rank() OVER (PARTITION BY Name ORDER BY Code) AS CodeRank
from MyTable

Then you can use the pivot functionality within either SQLServer or SSRS to format this as required.

Upvotes: 1

sq33G
sq33G

Reputation: 3360

Not exactly. You can hack around with the idea of concatenating lots of Code,Qty pairs into a single value for each record, though. I'm not sure what you're planning to do with multiple columns with the same name, even if such a thing were possible.

Upvotes: 0

Related Questions