locked
Crostab Query Runing Fails RRS feed

  • Question

  • I have problem with Crosstab Query.

    Data of Me is TABLE have struct

    tblData(Ma, Ngay, DiaChiO, GiaTri, TenThiTruong)

    with example data

    Ma Ngay DiaChiO  GiaTri TenThiTruong

     

    7 2011-01-01 00:00:00 E 600 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 E 100 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 E 200 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 F 5520 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 F 1000 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 F 500 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 G 3.312e+006 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 G 100000 Nguyễn Minh Đạo

    7 2011-01-01 00:00:00 G 400000 Nguyễn Minh Đạo

     

    Now I want output data

    Ma Ngay TenThiTruong E F G

    7  01-01-2011    Nguyen Minh Dao 600 500 3.312e+006

    7     01-01-2011    Nguyen Minh Dao       100 5520   100000

    7     01-01-2011    Nguyen Minh Dao       200 500   400000

     

    I have use Crosstab Query but results not true.

     

    Help me

     

    Thursday, June 23, 2011 9:56 AM

Answers

  • Try:

    
    
    DECLARE @cSQL Nvarchar(MAX), @SQL NVARCHAR(MAX)
    
    ;WITH CellCTE AS
     (SELECT DISTINCT DiaChiO as [CellAddress] FROM #tblColumnData)
    
    SELECT @cSQL = ISNULL(@cSQL + ',[', '[') + CellAddress + ']'
     FROM CellCTE
     ORDER BY [CellAddress]
     
    
    SET @SQL='SELECT Ma,Ngay,TenThiTruong'
    IF LEN(@cSQL)>0 SET @SQL=@SQL + ',' + @cSQL
    SET @SQL=@SQL + ' FROM (SELECT Ma,Ngay,TenThiTruong, GiaTri, DiaChio,
    DENSE_RANK() OVER (PARTITION BY Ma, NGay ORDER BY Seq %3) as NewSeq
    FROM ( SELECT *, ROW_NUMBER() over (partition by Ma, NGay order by DiaChio) as Seq 
    FROM #tblColumnData ) p) p1 '
    IF LEN(@cSQL)>0 SET @SQL=@SQL+' PIVOT(MAX(GiaTri) FOR DiaChiO IN (' + @cSQL + ')) AS pvt;'
    
    
    EXECUTE (@SQL)
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Thursday, June 30, 2011 2:55 AM
    Friday, June 24, 2011 3:24 AM

All replies

  • Can you properly re-format you example data? In form of a table.

    At least I cannot get what you've mentioned here and what kind of output you expect.

    Also why do you hesitate to provide full table DDL rather giving just column names?


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Thursday, June 23, 2011 10:17 AM
  • Example data for me


    Example Data in My TABLE

     Ma		Ngay		DiaChiO		GiaTri		TenThiTruong			
     7	 	 2011-01-01		 E		 600		Nguyen Minh Dao 	
     7	 2011-01-01		 		E	 	 100		Nguyen Minh Dao
     7	 2011-01-01		 E		 200		Nguyen Minh Dao	
     7	 2011-01-01		 F		 5520		Nguyen Minh Dao
     7	 2011-01-01		 F		 1000		Nguyen Minh Dao
     7	 2011-01-01		 F		 500		Nguyen Minh Dao
     7	 2011-01-01		 G		 3000		Nguyen Minh Dao
     7	 2011-01-01		 G		 1000		Nguyen Minh Dao
     7	 2011-01-01		 G		 4000		Nguyen Minh Dao

    Now I want Output has format
     Ma		Ngay		TenThiTruong		 E		F		 G
     7	 2001-01-01		Nguyen Minh Dao		600	 5520		3000
     7	 2001-01-01		Nguyen Minh Dao		100	 1000		1000
     7	 2001-01-01		Nguyen Minh Dao		200	 500		4000




    Help me slove problem

    thanks







    Thursday, June 23, 2011 2:21 PM
  • Well, you can do either dynamic pivot or static pivot. If the values E, F, G are static, then this simple PIVOT will work

     

    select Ma, Ngay, TenThiTruong, E, F, G from DataTable PIVOT (SUM(GiaTri) FOR DiaChio IN ([E],[F],[G])) pvt
    

     

    If E,F,G values are not static, then we're talking about dynamic pivot - just search this forum for examples - there are more than plenty.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 23, 2011 3:06 PM
  • I have using PIVOT Statement but results false

    Field GiaTri have type is NVARCHAR(MAX) -> not use function SUM in PIVOT Statement.

    I use function MAX then results little output I want because It SELECT record with value in field GiaTri = MAX(GiaTri).

    Help me

    Friday, June 24, 2011 2:11 AM
  • If using PIVOT with function SUM not display output results

    with example data for me then statement

     

    select Ma, Ngay, TenThiTruong, E, F, G from DataTable PIVOT (SUM(GiaTri) FOR DiaChio IN ([E],[F],[G])) pvt
    for results

    Ma		Ngay			TenThiTruong		E			F		 G
    7	2011-01-01	Nguyen Minh Dao	900		7020		8000

    Results I want
    Ma	  Ngay		TenThiTruong		 E	  F		 G
     7	 2001-01-01		Nguyen Minh Dao		600	 5520		3000
     7	 2001-01-01		Nguyen Minh Dao		100	 1000		1000
     7	 2001-01-01		Nguyen Minh Dao		200	 500		4000

     

    I use batch command:

    DECLARE @cSQL Nvarchar(MAX), @SQL NVARCHAR(MAX)
    
    ;WITH CellCTE AS
     (SELECT DISTINCT DiaChiO as [CellAddress] FROM #tblColumnData)
    
    SELECT @cSQL = ISNULL(@cSQL + ',[', '[') + CellAddress + ']'
     FROM CellCTE
     ORDER BY [CellAddress]
     
    
    SET @SQL='SELECT Ma,So,Ngay,DienGiai,SoThiTruong,TenThiTruong,ThiTruongID'
    IF LEN(@cSQL)>0 SET @SQL=@SQL+','+@cSQL
    SET @SQL=@SQL+' FROM ( SELECT * FROM #tblColumnData ) p '
    IF LEN(@cSQL)>0 SET @SQL=@SQL+' PIVOT(MAX(GiaTri) FOR DiaChiO IN (' + @cSQL + ')) AS pvt;'
    EXEC (@SQL)


    Help me

     


    Friday, June 24, 2011 2:19 AM
  • Try:

    
    
    DECLARE @cSQL Nvarchar(MAX), @SQL NVARCHAR(MAX)
    
    ;WITH CellCTE AS
     (SELECT DISTINCT DiaChiO as [CellAddress] FROM #tblColumnData)
    
    SELECT @cSQL = ISNULL(@cSQL + ',[', '[') + CellAddress + ']'
     FROM CellCTE
     ORDER BY [CellAddress]
     
    
    SET @SQL='SELECT Ma,Ngay,TenThiTruong'
    IF LEN(@cSQL)>0 SET @SQL=@SQL + ',' + @cSQL
    SET @SQL=@SQL + ' FROM (SELECT Ma,Ngay,TenThiTruong, GiaTri, DiaChio,
    DENSE_RANK() OVER (PARTITION BY Ma, NGay ORDER BY Seq %3) as NewSeq
    FROM ( SELECT *, ROW_NUMBER() over (partition by Ma, NGay order by DiaChio) as Seq 
    FROM #tblColumnData ) p) p1 '
    IF LEN(@cSQL)>0 SET @SQL=@SQL+' PIVOT(MAX(GiaTri) FOR DiaChiO IN (' + @cSQL + ')) AS pvt;'
    
    
    EXECUTE (@SQL)
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Thursday, June 30, 2011 2:55 AM
    Friday, June 24, 2011 3:24 AM