quote:

---

Originally posted by SlowlyLearning

Query 1
A A1
B A1
B A1
A B1
C B1
B B1
D C1
E C1
A C1

Query 2
A 3 A1 C1
B 2 A1 B1
C 1 B1 B1
D 1 C1 C1
E 1 C1 C1

Query 2 SQL
SELECT [query1].Col1,
Count([query1].col2) AS Count,
First([query1].[code1]) AS [FirstOfCode1],
Last([query1].[code1]) AS [LastOfCode1]
FROM [query 1]
GROUP BY [query 1].Col1;


What I want to do is list each of the column two entries of query 1 that make up the second column of query 2. For A I want to know that it is listed with A1, B1, and C1. I can use the first and last command to get the first and last entry but is there also a second, third, fourth, etc. command that I can use?

---

SELECT Col1,[Count],
MAX(CASE WHEN Seq=1 THEN Col2 ELSE NULL END) AS [FirstOfCode1],
MAX(CASE WHEN BSeq=1 THEN Col2 ELSE NULL END) AS [LastOfCode1]
FROM
(
SELECT [query1].Col1,
[query1].Col2,
COUNT([query1].col2) OVER (PARTITION BY [query1].Col1) AS [Count],
ROW_NUMBER() OVER(PARTITION BY [query1].Col1 ORDER BY [query1].col2) AS Seq,
ROW_NUMBER() OVER(PARTITION BY [query1].Col1 ORDER BY [query1].col2 DESC) AS BSeq
FROM YourTable
)t
GROUP BY Col1,[Count]

quote:

---

Originally posted by SlowlyLearning

The actual order doesn't matter to me I just want to know which values in the 'Type' category from table one make up the 'count of type' category from table two. In this case A1 B1 and C1 are the Type entries that make up the Category A. I am using MS Access and in the program you can use the commands first and last to pull up the first and last entries A1 and C1 respectively but I want the ability to pull up each entry in a seperate column as shown in my second post.

---

SELECT Col1,[Count],
MAX(CASE WHEN Seq=1 THEN Col2 ELSE NULL END) AS [FirstOfCode1],
MAX(CASE WHEN Seq=2 THEN Col2 ELSE NULL END) AS [SecondOfCode1],
MAX(CASE WHEN Seq=3 THEN Col2 ELSE NULL END) AS [ThirdOfCode1],
MAX(CASE WHEN Seq=4 THEN Col2 ELSE NULL END) AS [FourthOfCode1],
MAX(CASE WHEN Seq=5 THEN Col2 ELSE NULL END) AS [FifthOfCode1],
MAX(CASE WHEN Seq=6 THEN Col2 ELSE NULL END) AS [SixthOfCode1],
....
FROM
(
SELECT [query1].Col1,
[query1].Col2,
COUNT([query1].col2) OVER (PARTITION BY [query1].Col1) AS [Count],
ROW_NUMBER() OVER(PARTITION BY [query1].Col1 ORDER BY [query1].col2) AS Seq
FROM YourTable
)t
GROUP BY Col1,[Count]