[the database structure]

The cdate with type is a primary key,
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Cdate type data1 data2
1001 a 1 1
1001 b 2 2
1001 c 3 3
1002 a 4 4
5 5 1002 b
1002 c 6 6
...
1001 k
1002 f
1003 e
...
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

[query target]

Want to get the type a, b, c data1 and data2


Expect the results
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --




Should consult everybody a great god, and how to write?



PS:
Actual demand is, every time to query type, type is not fixed, the number is not fixed, want to do a general query capabilities,

CodePudding user response:

Assume that the table name is A, use the query - new query crosstab query wizard automatically generated

 TRANSFORM First ([A query]. Data1) AS the First record of data1 
SELECT [A query] cdate 
The FROM [SELECT A.c date, "type" + A.t ype + "data 1" as the type, a. d. ata1 
FROM A 
UNION ALL SELECT A.c date, "type" + A.t ype + 2 "" data, a. d. ata2 
FROM A] AS [A query] 
GROUP BY [A query] cdate 
The PIVOT [r]. A query TYPE; 

A query is for the sake of data table first organized into three columns

CodePudding user response:

 TRANSFORM First ([A query]. Data1) AS the First record of data1 
SELECT [A query] cdate 
The FROM (SELECT A.c date, "type" + A.t ype + "data 1" as the type, a. d. ata1 
FROM A 
UNION ALL SELECT A.c date, "type" + A.t ype + 2 "" data, a. d. ata2 
FROM A) AS [A query] 
GROUP BY [A query] cdate 
The PIVOT [r]. A query TYPE;