SQL Server query split table

Question

Not sure how it calls, I need to do this:

+---------------+
| param | value |
|   1   |   3   |
|   1   |   3   |
|   2   |   4   |
|   2   |   4   |
+-------+-------+

to

+-----------------+
| param1 | param2 |
|    3   |    4   |
|    3   |    4   |
+-------+---------+

Is it possible without INNER JOIN ?

Where is my error?

SELECT TOP 10 
    dbo.RW_ReceivedData.[Value] AS Value1, 
    dbo.RW_ReceivedData.[Value] AS Value2
FROM 
    RW_ReceivedData
WHERE 
    dbo.RW_ReceivedData.LogicalName = CAST(0x01000F0800FF AS varbinary(8000)),  
    dbo.RW_ReceivedData.LogicalName = CAST(0x01000F0800FF AS varbinary(8000))

Answer 1

This should work:

DECLARE @cols NVARCHAR(4000) 
= STUFF(
    (
        SELECT  DISTINCT ',[' + CAST(param AS VARCHAR(10)) + ']'
        FROM    tbl
        FOR XML PATH('')
    ),1,1,'')

DECLARE @colNames NVARCHAR(4000) 
= STUFF(
    (
        SELECT  DISTINCT ',[' + CAST(param AS VARCHAR(10)) + '] AS param' + CAST(param AS VARCHAR(10))
        FROM    tbl
        FOR XML PATH('')
    ),1,1,'')

DECLARE @sql NVARCHAR(4000) = 
'
SELECT  '+@colNames+'
FROM    
(
    SELECT  *, ROW_NUMBER() OVER (PARTITION BY param ORDER BY param) n
    FROM    tbl
) t
PIVOT   
(
    MIN(value) FOR param IN ('+@cols+')
) pvt'

EXEC(@sql)

Here is SQL Fiddle

This approach uses dynamic PIVOT. In order to get the expected result ROW_NUMBER() with partitioning was used since values in param column are not unique.

UPDATE

You can also try it like this, but you have to manually add columns to the query:

SELECT  SUM(CASE WHEN param = 1 THEN value ELSE 0 END) param1
        , SUM(CASE WHEN param = 2 THEN value ELSE 0 END) param2
        , SUM(CASE WHEN param = 3 THEN value ELSE 0 END) param3
FROM
    (
        SELECT  *
                , ROW_NUMBER() OVER (PARTITION BY param ORDER BY param) num
        FROM    tbl
    ) t
GROUP BY
        num

SQL Fiddle

If you don't want columns to be automatically handled, you can just use static PIVOT:

SELECT  [1] AS param1
        ,[2] AS param2  
FROM    (      
    SELECT  *
            , ROW_NUMBER() OVER (PARTITION BY param ORDER BY param) n      
    FROM    tbl) t  
PIVOT   (      
    MIN(value) FOR param IN ([1],[2])  
) pvt