SQL query for group by result in different column [closed]

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

Answer

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)