CODEWITHSUNDEEP
c#sqlms-accesspivot
Mana
Mana

Reputation: 1929

convert rows to columns in Access

I have read many question on Stack Overflow related to my problem, but I don't think they quite address my problem. Basically I download a XML dataset with lots of data, and inserted that data into my MS Access database. What I want to do is convert the data so that some specific rows become columns.

Now I can probably do this manually in code before inserting the data to database, but that would require lots of time and change in code, so I'm wondering if its possible to do this with MS Access.

Here's how my table basically looks, and how I want to convert it.

The index is not so relevant in my case

[Table1]               =>              [Table1_converted]

[Index]  [Name]   [Data]  [NameID]      [NameID]  [AA]    [BB]    [CC]    [DD]   
 1        AA       14      1             1         14     date1    64      61   
 2        BB(date) 42      1             2        15+19   date2   67+21   63+12
 3        CC       64      1             3          9              10    
 4        DD       61      1             4                date4     1      87
 5        AA       15      2                 
 6        BB(date) 35      2
 7        CC       67      2        
 8        DD       63      2
 9        AA       9       3
10        CC       10      3
11        AA       19      2                
12        BB(date) 20      2
13        CC       21      2        
14        DD       12      2
15        BB(date) 83      4
16        CC       1       4
17        DD       87      4

Forgot to mention that, the Values under the column [Name] are not really AA BB CC. They are more complex then that. AA is actually like "01 - NameAA", without the quotation mark.

Forgot to mention one important element in my question, if the [Name] ex. AA with same [NameID] exists in table, then the [Data] should SUM up those two values. I have edited the tables, on the converted table i have written ex. 15+19 or 35+20 which only illustrates which values are summed up.

One more edit, hopefully the last. One of the [Name] BB has a Datetime type in [Data]. The NameID can be whichever, does not matter. So i need a query which does an exception on [Name] BB when its summing up, so that it does not sum it up like it does to every other [Name]s [Data]. Places where date is written multiple times for same [Name] and [NameID], it is always the same.

Upvotes: 1

Views: 3910

Answers (4)

Gord Thompson
Gord Thompson

Reputation: 123609

To accomplish this in Access, all you need to do is

TRANSFORM Sum([Data]) AS SumOfData
SELECT [NameID]
FROM [Table1]
GROUP BY [NameID]
PIVOT [Name]

edit re: revised question

To handle some [Name]s differently we would need to assemble the results (Sum()s, etc.) first, and then crosstab the results

For test data in [Table1]:

Index  Name  Data        NameID
-----  ----  ----------  ------
    1  AA    14               1
    2  BB    2013-12-01       1
    3  CC    64               1
    4  DD    61               1
    5  AA    15               2
    6  BB    2013-12-02       2
    7  CC    67               2
    8  DD    63               2
    9  AA    9                3
   10  CC    10               3
   11  AA    19               2
   12  BB    2013-12-02       2
   13  CC    21               2
   14  DD    12               2
   15  BB    2013-12-04       4
   16  CC    1                4
   17  DD    87               4

the query

TRANSFORM First(columnData) AS whatever
SELECT [NameID]
FROM
    (
            SELECT [NameID], [Name], Sum([Data]) AS columnData
            FROM [Table1]
            WHERE [Name] <> 'BB'
            GROUP BY [NameID], [Name]
        UNION ALL
            SELECT DISTINCT [NameID], [Name], [Data]
            FROM [Table1]
            WHERE [Name] = 'BB'
    )
GROUP BY [NameID]
PIVOT [Name]

produces

NameID  AA  BB          CC  DD
------  --  ----------  --  --
     1  14  2013-12-01  64  61
     2  34  2013-12-02  88  75
     3  9               10    
     4      2013-12-04  1   87

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

DECLARE @Table1 TABLE ([Index] INT,[Name] CHAR(2),[Data] INT,[NameID] INT) 

INSERT INTO @Table1
VALUES
 (1,'AA',14,1),              
 (2,'BB',42,1),            
 (3,'CC',64,1),             
 (4,'DD',61,1),             
 (5,'AA',15,2),                
 (6,'BB',35,2),
 (7,'CC',67,2),        
 (8,'DD',63,2),
 (9,'AA',9,3),
(10,'CC',10,3),
(11,'BB',83,4),
(12,'CC',1,4),
(13,'DD',87,4)

SELECT [NameID] , ISNULL([AA], '') AS [AA], ISNULL([BB], '') AS [BB]
        , ISNULL([CC], '') AS [CC], ISNULL([DD], '') AS [DD]
FROM  
    (
    SELECT NAME, DATA, NAMEID
    FROM @Table1
    )q
    PIVOT
        (
        SUM(DATA)
        FOR NAME
        IN ([AA], [BB], [CC], [DD])
        )P

Result Set

NameID  AA    BB    CC  DD
1       14    42    64  61
2       15    35    67  63
3       9           10  
4             83    1   87

Upvotes: 1

Dhaval
Dhaval

Reputation: 2861

I think you need to this

1) Take all your Table1 as it is in SQL Server

2) Then run following query

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Name) 
                    from [Table1]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT countryid,' + @cols + ' 
            from 
            (
                select NameID, Name 
                from Table1 cc                
            ) T 
            pivot 
            (
                max (Name)
                for languagename in (' + @cols + ')
            ) p '

execute sp_executesql @query;

Upvotes: 1

Sunil Naudiyal
Sunil Naudiyal

Reputation: 334

Try this...in sql query may be it is your answer

   SELECT NameID , [AA] as AA,[BB] as BB,[CC] as CC,[DD]  as DD     
        FROM        
        (
            SELECT Name,Data,NameID FROM Table1
        )PivotData

        PIVOT
        (
            max(Data) for Name in ([AA],[BB],[CC],[DD])     
        ) AS Pivoting

Upvotes: 1

Related Questions