SQL query for group by result in different column

Question

I Have table having 2 columns

Column1 Value
   A     10 
   B     20 
   C     11
   A     40 
   B     30 
   C     22

I Want Result like

Column1 Value1 Value2
A       10      40
B       20      30
C       11      22

Is there any query or store Procdure to get such Output

Question is not clear. You have two columns, make it clear on what values are there in column 1 and column 2 — rootkonda, Feb 19 '20 at 14:10
If you add more data points it will be clear. Right now it seems like you want min and max of the values. — qwerty, Feb 19 '20 at 14:14
Dear, I've deleted my answer because you have wirtten in the comment you want Value1, Valu2, Value3 and so on. Maybe you can use a pivot function — Joe Taras, Feb 19 '20 at 14:15
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Tschallacka, Feb 19 '20 at 14:15
What if there are 3 values for A? Actually, what if there are multiple records of A,b and C and not just 2. — qwerty, Feb 19 '20 at 14:15
@qwerty it should create Value3 column and show 3rd value of A — prashanat chaudhari, Feb 19 '20 at 14:17
Dear, you must add the third value in your data set, for A or B or C, so we exactly know want you want as result set. — Joe Taras, Feb 19 '20 at 14:17
@prashanatchaudhari: Yes, but when I'll have a third value? otherwise the answer MIN MAX is ok — Joe Taras, Feb 19 '20 at 14:27
@prashanatchaudhari, when a question accumulates this many comments asking for clarification, it's a sure sign that there wasn't enough information in the question for the community to help you arrive at an answer. — Eric Brandt, Feb 19 '20 at 15:15

score 3 · Answer 1 · answered Feb 19 '20 at 14:28

If there's more then 2 Value columns then MIN & MAX won't do.

Then you can use ROW_NUMBER to calculate numbered values, and use that in a PIVOT

SELECT *
FROM
(
    SELECT Column1, [Value]
    , Col = CONCAT('Value', ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY [Value]))
    FROM YourTable
) Src
PIVOT (
    MAX([Value])
     -- add more columns here
    FOR Col IN (Value1, Value2)
) Pvt

score 3 · Answer 2 · answered Feb 19 '20 at 14:43

If you don't know for sure how many values per Column1 there will be, you'll have to do dynamic SQL.

Adapting @LukStorms excellent answer above:

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
    DROP TABLE #MyTable


create table #MyTable (c nvarchar(1), v int)

insert into #MyTable
select 'A',10
union select 'B',20
union select 'C',11
union select 'A',40
union select 'B',30
union select 'C',22
union select 'A',60
union select 'C',28
union select 'C',150

declare @s varchar(max), @maxVals int, @i int = 1

select @maxVals = max(ct) from (select c,count(*) as ct from #MyTable group by c) as x

set @s = 'SELECT *
FROM
(
    SELECT c as Column1, v
    , Col = CONCAT(''Value'', ROW_NUMBER() OVER (PARTITION BY c ORDER BY v))
    FROM #MyTable
) Src
PIVOT (
    MAX(v)
    FOR Col IN ('

while @i <= @maxVals
begin

set @s = @s + 'Value' + cast(@i as varchar)

if @i < @maxVals
     set @s = @s + ', '

set @i = @i +1
end

set @s = @s + ')
) Pvt'

exec(@s)

Note that SQL won't necessarily keep the order the values were inserted, unless you use an identity column and sort on that. Also, if a column1 has more than one value the same, it will ignore the duplicate.

Hope that helps, and thanks @LukStorms for the initial work

score 2 · Answer 3 · answered Feb 19 '20 at 14:49

It can also be done dynamically as bellow since you are not sure of the number of columns by modifying @LukStorms answer

create table #MyTable (Column1 VARCHAR(2), [Value] INT)
insert into #MyTable values
('A',10),('B',20),('C',11),
('A',40), ('B',30), ('C',22)



 DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONCAT('Value', ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY [Value]))) 
            FROM #MyTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query =  'SELECT 
                * 
              FROM
            ( SELECT Column1, [Value],
            CONCAT(''Value'', ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY [Value])) AS ColValue
                FROM #MyTable
            ) Src
            PIVOT (
                MAX([Value]) FOR ColValue IN ('+@cols+')  
) Pvt'

 exec(@query)

 drop table #MyTable

score 1 · Accepted Answer · answered Feb 19 '20 at 14:53

You require DynamicSql + Pivot operation.I am not aware of any other method.

The below query will give you what you want.

DROP TABLE tmptbl
GO
SELECT * ,
    'Value' + (CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (PARTITION BY [Column1] ORDER BY Column1,[Value]))) [ValueColumn]
INTO tmptbl
FROM (
SELECT 'A' [Column1],10 [Value]
UNION ALL
SELECT 'A' [Column1],20 [Value]
UNION ALL
SELECT 'A' [Column1],30 [Value]
UNION ALL
SELECT 'B' [Column1],40 [Value]
UNION ALL
SELECT 'B' [Column1],50 [Value]
UNION ALL
SELECT 'B' [Column1],60 [Value]
UNION ALL
SELECT 'B' [Column1],70 [Value]
) tbl
GO
DECLARE @Vals NVARCHAR(MAX)
SET @Vals = N''
SELECT @Vals = STUFF((SELECT ', ' + QUOTENAME('Value' + CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY [Valuecolumn])))
                FROM tmptbl
                GROUP BY [Valuecolumn]
                FOR XML PATH ('')),1,1,'')
SELECT @vals

DECLARE @strsql NVARCHAR(MAX) = ' SELECT [Column1],' + @vals + ' 
                                  FROM (
                                     SELECT [Column1],[Value],[ValueColumn]
                                     FROM tmptbl
                                       ) t
                                  PIVOT (
                                  AVG([Value])
                                  FOR [ValueColumn] IN (' + @Vals + ')) As p'
EXEC (@strsql)

SQL query for group by result in different column

4 Answers4