none
动态行转列 sql怎么写 RRS feed

  • 问题

  • 动态行转列 sql怎么写

    表 table

    id prodid lieming value  dingdan

    1 p01       数量    50      ding1

    2 p01       价格    10      ding1

    3 p01       赠送数量    1   ding1

    4 p02       数量    50       ding1

    5 p02       价格    10       ding1

    6 p02            赠送数量  0  ding1

    类似这样的, 不过lieming是不固定的 可以动态添加的,可以添加5列 10列 20列 所以 数量、价格这些是不能写死的,最后dingdan查询出来, 具体的这个sql应该怎么写? 万分感谢了


    学习中...

    2012年11月20日 7:13

答案

  • CREATE TABLE [dbo].[Table_2](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [prodid] [nchar](10) NULL,
     [lieming] [nchar](10) NULL,
     [value] [decimal](18, 2) NULL
    ) ON [PRIMARY]

    GO
    insert into  [dbo].[Table_2] values('p01','数量',50)
    insert into  [dbo].[Table_2] values('p01','价格',10)
    insert into  [dbo].[Table_2] values('p01','赠送数量',1)
    insert into  [dbo].[Table_2] values('p02','数量',50)
    insert into  [dbo].[Table_2] values('p02','价格',10)
    insert into  [dbo].[Table_2] values('p02','赠送数量',1)
    insert into  [dbo].[Table_2] values('p03','税率',10)
    insert into  [dbo].[Table_2] values('p04','价格',1)
    insert into  [dbo].[Table_2] values('p04','税率',1)
    go

    Declare @Script as Varchar(8000);
    Declare @Script_prepare1 as Varchar(8000);
    Declare @MultiColumns as Varchar(8000)
    set @Script='';
    set @Script_prepare1='';
    set @MultiColumns =
    (SELECT distinct '['+[lieming]+']'+','  FROM [dbo].[Table_2] 
    FOR XML PATH(''));
        
    set @MultiColumns= substring(@MultiColumns,1,len(@MultiColumns)-1)

    Set @Script_prepare1 = '
    SELECT [prodid], ? 
    FROM
    (SELECT  [prodid], [lieming], [value]
    FROM  [dbo].[Table_2]) p
    PIVOT
    (
    max ([value] )
    FOR [lieming] IN
     (
     ? 
     )
    ) AS pvt
    ORDER BY pvt. [prodid];';
    Select   @Script = @Script + Replace(@Script_prepare1, '?', @MultiColumns)
    Exec (@Script)



    Please click the Mark as Answer button if a post solves your problem!

    2012年11月20日 14:23

全部回复

  • CREATE TABLE [dbo].[Table_1](
     [id] [int] NULL,
     [prodid] [nchar](10) NULL,
     [lieming] [nchar](10) NULL,
     [value] [int] NULL
    ) ON [PRIMARY]

    GO

     insert into [dbo].[Table_1] values(1,'P01',' 数量 ',50)

     insert into [dbo].[Table_1] values(2,'P01',' 价格 ',10)

     insert into [dbo].[Table_1] values(3,'P01',' 赠送数量  ',1)

     insert into [dbo].[Table_1] values(4,'P02',' 数量  ',50)

     insert into [dbo].[Table_1] values(5,'P02',' 价格',10)

     insert into [dbo].[Table_1] values(6,'P02',' 赠送数量  ',0)

    go

     SELECT [prodid],
    (SELECT [lieming]+ cast([value] as varchar) +','  FROM [dbo].[Table_1] 
      WHERE [prodid]=A.[prodid]
      FOR XML PATH('')) AS dingdan
    FROM [dbo].[Table_1]  A
    GROUP BY [prodid]



    Please click the Mark as Answer button if a post solves your problem!

    2012年11月20日 7:52
  • 谢谢 我先试下

    学习中...

    2012年11月20日 8:31
  • 缺少右括号 位置不对

    学习中...

    2012年11月20日 8:45
  • 缺少右括号 位置不对

    学习中...


    没有明白啊,你具体给个你想要的结果给出来


    Please click the Mark as Answer button if a post solves your problem!

    2012年11月20日 8:47
  • dingdan查询出来结果是

    proid 数量 价格  赠送数量 dingdan(这列可以不显示)

    p01  50    10    1            ding1

    p02  50    10    0            ding1


    学习中...

    2012年11月20日 8:51
  • lieming 列数是不固定的


    学习中...

    2012年11月20日 8:55
  • lieming 列数是不固定的


    学习中...

    很遗憾,SQL SERVER可能无法实现你的需求,因为SQL SERVER不支持锯齿结果集

    你的真正需求是什么?为什么需要这样的锯齿结果集?



    Please click the Mark as Answer button if a post solves your problem!

    2012年11月20日 10:24
  • 动态配置列,把相应的数据添加到一个表里面  查询的时候行转列


    学习中...

    2012年11月20日 12:39
  • 动态配置列,把相应的数据添加到一个表里面  查询的时候行转列


    学习中...


    这并不是你真正的需求吧,行转列之后呢,之后干嘛,为什么要行转列?


    Please click the Mark as Answer button if a post solves your problem!

    2012年11月20日 12:42
  • 关注一下

    lieming:列名

    dingdan:订单


    给我写信: QQ我:点击这里给我发消息


    2012年11月20日 12:44
  • 是真正的需求,

    一般情况下我们都是把列名 有多少列 都开始在数据库表里面写死了的, 我这样做的好处就是客户可以根据自己的要求动态添加列名和列数比如添加10列

      我设计表的时候是这样的

    table 

    id 产品id 列名列的值 订单id   

    数据如下

    id prodid lieming value  dingdan

    1 p01       数量    50      ding1

    2 p01       价格    10      ding1

    3 p01       赠送数量    1   ding1

    4 p02       数量    50       ding1

    5 p02       价格    10       ding1

    6 p02            赠送数量  0  ding1

    把所有添加的时候都保存到这个表里面, 查询的时候行转列 就达到下面的显示效果

    产品id 数量 价格  赠送数量 订单id(这列可以不显示)

    p01  50    10    1            ding1

    p02  50    10    0            ding1


    学习中...

    2012年11月20日 12:50
  • 列名不固定的就不好搞了

    给我写信: QQ我:点击这里给我发消息

    2012年11月20日 12:50
  • 是的 列名 是从另外一个配置表里面读取出来的 ,客户需要多少列就自己配置

    学习中...


    • 已编辑 猫子 2012年11月20日 12:52
    2012年11月20日 12:51
  • 是真正的需求,

    一般情况下我们都是把列名 有多少列 都开始在数据库表里面写死了的, 我这样做的好处就是客户可以根据自己的要求动态添加列名和列数比如添加10列

      我设计表的时候是这样的

    table 

    id 产品id 列名列的值 订单id   

    数据如下

    id prodid lieming value  dingdan

    1 p01       数量    50      ding1

    2 p01       价格    10      ding1

    3 p01       赠送数量    1   ding1

    4 p02       数量    50       ding1

    5 p02       价格    10       ding1

    6 p02            赠送数量  0  ding1

    把所有添加的时候都保存到这个表里面, 查询的时候行转列 就达到下面的显示效果

    产品id 数量 价格  赠送数量 订单id(这列可以不显示)

    p01  50    10    1            ding1

    p02  50    10    0            ding1


    学习中...

    假如再来2个  p03,p04

    id prodid lieming value dingdan

    7  p03      产地      上海     ding1

    8  p04      成本       10      ding1

    那你的p01,p02,p03,p04结果集准备怎么显示?



    Please click the Mark as Answer button if a post solves your problem!


    2012年11月20日 12:55
  • 产品id 数量 价格  赠送数量 订单id  产地 成本

    p01  50    10    1            ding1       0

    p02  50    10    0            ding1      0

    p03  50    10    0            ding1   上海   0

    p04  0       0     0            ding1      10

    列名是从配置表里面读取的,凡是配置表有的列名 就必须录入值没有值就保存空值或者0


    学习中...

    2012年11月20日 12:59
  • CREATE TABLE [dbo].[Table_2](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [prodid] [nchar](10) NULL,
     [lieming] [nchar](10) NULL,
     [value] [decimal](18, 2) NULL
    ) ON [PRIMARY]

    GO
    insert into  [dbo].[Table_2] values('p01','数量',50)
    insert into  [dbo].[Table_2] values('p01','价格',10)
    insert into  [dbo].[Table_2] values('p01','赠送数量',1)
    insert into  [dbo].[Table_2] values('p02','数量',50)
    insert into  [dbo].[Table_2] values('p02','价格',10)
    insert into  [dbo].[Table_2] values('p02','赠送数量',1)
    insert into  [dbo].[Table_2] values('p03','税率',10)
    insert into  [dbo].[Table_2] values('p04','价格',1)
    insert into  [dbo].[Table_2] values('p04','税率',1)
    go

    Declare @Script as Varchar(8000);
    Declare @Script_prepare1 as Varchar(8000);
    Declare @MultiColumns as Varchar(8000)
    set @Script='';
    set @Script_prepare1='';
    set @MultiColumns =
    (SELECT distinct '['+[lieming]+']'+','  FROM [dbo].[Table_2] 
    FOR XML PATH(''));
        
    set @MultiColumns= substring(@MultiColumns,1,len(@MultiColumns)-1)

    Set @Script_prepare1 = '
    SELECT [prodid], ? 
    FROM
    (SELECT  [prodid], [lieming], [value]
    FROM  [dbo].[Table_2]) p
    PIVOT
    (
    max ([value] )
    FOR [lieming] IN
     (
     ? 
     )
    ) AS pvt
    ORDER BY pvt. [prodid];';
    Select   @Script = @Script + Replace(@Script_prepare1, '?', @MultiColumns)
    Exec (@Script)



    Please click the Mark as Answer button if a post solves your problem!

    2012年11月20日 14:23
  • 谢谢了 辛苦了

    学习中...

    2012年11月21日 9:19
  • 曾经想了很久都没有想到怎么解决

    今天瞬间就想到了 现发完整代码 以作保存

    /// <summary>
            /// 根据orderbillid查询相应的明细列表
            /// </summary>
            /// <param name="ID"></param>
            /// <param name="ProductIDS"></param>
            public DataTable GetDetailData(string ID, string companyid)
            {
                string sql = @"SELECT ProductName,";
                string strsql = "SELECT KPIID FROM orderbillconfig WHERE state=1 AND CREATECOMPANY=:CREATECOMPANY order by orderid";
                using (Database db = DatabaseFactory.CreateDatabase())
                {
                    db.ClearParam();
                    db.AddParam(":CREATECOMPANY", companyid);
                    DataTable dt = db.ExecuteDataTable(strsql, CommandType.Text);
                    if (dt != null)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            sql += "max(CASE  WHEN TargetKPI.ID = '" + dr["KPIID"].ToString() + "' THEN inputvalue END) AS KPIName,";
                        }
                    }
                   
                }
                sql = sql.Remove(sql.LastIndexOf(","), 1);
                sql += @" FROM orderbillconfig
                    LEFT JOIN OrderBillDetail ON orderbillconfig.KPIID = ORDERBILLDETAIL.KPIID
                    INNER JOIN TargetKPI ON orderbillconfig.KPIID = TargetKPI.ID
                    INNER JOIN Product ON OrderBillDetail.PRODUCTID = Product.ID
                    WHERE orderbillconfig.state=1
                    AND OrderBillDetail.ORDERBILLID=:ID
                    AND orderbillconfig.CREATECOMPANY=:CREATECOMPANY group by ProductName";
                using (Database db = DatabaseFactory.CreateDatabase())
                {
                    db.ClearParam();
                    db.AddParam(":ID", ID);
                    db.AddParam(":CREATECOMPANY", companyid);
                    sql = sql.Replace("\r\n", " ").Replace('\n', ' ');
                    DataTable dt = db.ExecuteDataTable(sql, CommandType.Text);
                    return dt;
                }
            }
            /// <summary>
            /// 根据orderbillid查询相应的明细列表表头
            /// </summary>
            /// <param name="ID"></param>
            /// <param name="ProductIDS"></param>
            public DataTable GetDetailheaderData(string companyid)
            {
                string strsql = "SELECT KPIName FROM orderbillconfig INNER JOIN TargetKPI ON orderbillconfig.KPIID = TargetKPI.ID WHERE orderbillconfig.state=1 AND orderbillconfig.CREATECOMPANY=:CREATECOMPANY order by orderid";
               
                using (Database db = DatabaseFactory.CreateDatabase())
                {
                    db.ClearParam();
                    db.AddParam(":CREATECOMPANY", companyid);
                    DataTable dt = db.ExecuteDataTable(strsql, CommandType.Text);
                    return dt;
                }
            }


    学习中...

    2012年11月27日 8:28