1

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.

Improve this question
3
  • Looks like you want a pivot table. Commented Dec 3, 2013 at 9:19
  • Access SQL (Access) and T-SQL (SQL Server) are very different. Is [Table1] a native Access table? If so, then the 'sql-server' tag should be removed from this question. Commented Dec 3, 2013 at 10:23
  • removed the sql tag. I chose it thinking it was a generic term Commented Dec 3, 2013 at 10:51

4 Answers 4

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

OMG, that was a really nice and simple solution. It did exactly what i needed. Thanks mate.
Hi, i was a little hasty with my question. There is one more element i forgot to add to my question. It is basically if i have multiple Names example AA with same NameID example 1. I would like to them to be summed up together so that the converted table only shows the SUM of all AAs with same NameID. Ill change the question to visually show what i mean.
@Mana That's easy, we just change the query to use Sum(). I have updated my answer.
Thanks, testing it now.
Hi again, so i have tested now your solution, and it does work, but there is one small problem. The thing is, some of the [Data] contains Datetime type, don't ask my why. And i know for certain that only the [Name] BB contains them, and some are null whatever the [NameID]. So i was wondering if it is somehow possible to filter so that only the [Name] BB containing the Datetime under [Data] is not summed up? – Mana 7 mins ago
|
1

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
Improve this answer

4 Comments

forgot to mention that the square brackets[ ] just indicate that this is a column. The original name is without the square brackets [ ]
Could you check my update/edit of my question and tell me if this still applies?
then use [01 - NameAA] in palce of [AA] and same as rest of all fields
I have gone with Gord Thompsons solution. it was really simple and easy to use. Thanks for your effort though.
1

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;
Improve this answer

2 Comments

Could you check my update/edit of my question and tell me if this still applies?
I have gone with Gord Thompsons solution. it was really simple and easy to use. Thanks for your effort though.
1 
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
Improve this answer

8 Comments

The result values under DD doesn't seem to be correct, in your case, or was it just a misspelling?
@Mana ops its not incorrect but its the table format let me fix it they are pushed under CC but answer is definitely correct.
Could you check my update/edit of my question and tell me if this still applies?
@Mana if you dont want nulls to be shown just use the ISNULL function wait I will update my answer have a look then.
@Mana Have a look now I have updated my answer replaced nulls with empty string and formatted it. I hope it helps :)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.