How to get the data of specific user in one row

Question

If I have table with a structure like that :

serial    regnum    value    type

  1         55      100     normal

  2         55      66      Light

  3         77      70      normal

  4         30      40      Light

---

Now if i want to get the data concerning the regnum = 55 in one record not two with the following structure :

regnum  normal   light

 55       100      66

How to do this through sql query or through LINQ ?

Answer 1

You may have to use a pivot logic. Here is an example, where a DataTable is pivoted to convert row values into columns.

Dynamic PIVOT using C# Linq

Answer 2

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.type) 
            FROM table_name c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT regnum, ' + @cols + ' from 
            (
                select regnum
                    , value
                    , type
                from table_name
           ) x
            pivot 
            (
                 sum(value)
                for type in (' + @cols + ')
            ) p '


execute(@query)

Answer 3

select * from TABLE_NAME pivot (sum(value) for type in (Light,normal)) AS PVTTable where regnum=55

Answer 4

SELECT regnum, SUM(ISNULL(Light,0)) AS Light, SUM(ISNULL(normal,0)) AS Normal     
FROM 
    temp_sc  
  PIVOT (sum(value) FOR Type IN (Light,normal)) AS PVTTable
WHERE regnum=55
GROUP BY regnum

Answer 5

mysql query = "SELECT * FROM TABLE_NAME WHERE regnum = 55"

above will get all data with regnum = 55.

to be more specific:

mysql query = "SELECT * FROM TABLE_NAME WHERE regnum =55 AND type="Light" "

to get regnum 55 with type light