如何在一行中获取特定用户的数据[重复]

This question already has an answer here:

这个问题已经有了答案:

MS Access fields as columns 2 answers
MS访问字段作为列2的答案

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 = 55的数据，而不是在两个记录中获取以下结构:

regnum  normal   light

 55       100      66

How to do this through sql query or through LINQ ?

如何通过sql查询或LINQ实现这一点?

5 个解决方案

#1

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

你可能需要使用主逻辑。这里有一个例子，其中的DataTable可以将行值转换为列。

Dynamic PIVOT using C# Linq

使用c# Linq动态轴心

#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)

#3

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

从TABLE_NAME pivot (sum(value)中选择*作为PVTTable，其中regnum=55

#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

#5

-1

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

above will get all data with regnum = 55.

上面将获取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

使用类型灯获得regnum 55

#1