sql select record based on multiple "time" only

Question

I get following table "myTable" in Excel.

Time	A	B	C
0:01:01	Apple	1	2
0:01:01	Banana	3	4
0:01:01	Cherry	5	6
0:02:01	Apple	11	12
0:02:01	Banana	13	14
0:02:01	Cherry	15	16
0:03:01	Apple	21	22
0:03:01	Banana	23	24
0:03:01	Cherry	25	26

Want to get it converted to

Time	A	B	C	A	B	C	A	B	C
0:01:01	Apple	1	2	Banana	3	4	Cherry	5	6
0:02:01	Apple	11	12	Banana	13	14	Cherry	15	16
0:03:01	Apple	21	22	Banana	23	24	Cherry	25	26

So far I tried following steps in vain

1. Find distinct "A"s value using "SELECT DISTINCT A FROM myTable"
2. Find distinct "time" value using "SELECT DISTINCT Time FROM myTable ORDER BY Time ASC"
3. For each value of "A", I was trying to get entry for each time "SELECT * FROM myTable WHERE A in ("Apple") AND Time in ("0:01:01", "0:02:01", "0:03:01")" -> This query fails My approach if for each value of "A" get the table and update excel sheet column wise. i.e. First query for "Apple" against all "time". Update this value in Excel. Second Query for "Banana" against all time" and update after Apple update

Any help please.

• Time column consists of Time only
• Using ADODB connection within EXCEL using VBA script. I presume ADODB uses MS SQL

Thanks

Note : some of my past questions were not well received. Hence I will get blocked if I post similar questions. If you feel the same about this question, please do not mark it as wrong question. Instead do not answer this question. Thanks

Answer 1

Doing this in a loop is the wrong way to go about. I would also avoid using column names like time. It is a datatype name and should be avoided as it just makes things more difficult. And in this case it is too ambiguous to be useful. What does the value indicate? TimeCreated? TimePurchased? TimeUpdated? SomeOtherTime? Also having the same column name multiple times indicates a design issue but I will let you sort that one out.

Here is how you would retrieve this information using conditional aggregation from your sample data.

declare @Something table
(
    MyTime time
    , A varchar(20)
    , B int
    , C int
)
insert @Something values
('0:01:01', 'Apple', 1, 2)
, ('0:01:01', 'Banana', 3, 4)
, ('0:01:01', 'Cherry', 5, 6)
, ('0:02:01', 'Apple', 11, 12)
, ('0:02:01', 'Banana', 13, 14)
, ('0:02:01', 'Cherry', 15, 16)
, ('0:03:01', 'Apple', 21, 22)
, ('0:03:01', 'Banana', 23, 24)
, ('0:03:01', 'Cherry', 25, 26);

with MyCte as
(
    select *
        , RowNum = ROW_NUMBER() over(partition by s.MyTime order by s.B)
    from @Something s
)
select [Time] = max(c.MyTime)
    , A = max(case when c.RowNum = 1 then A end)
    , B = max(case when c.RowNum = 1 then B end)
    , C = max(case when c.RowNum = 1 then C end)
    , A = max(case when c.RowNum = 2 then A end)
    , B = max(case when c.RowNum = 2 then B end)
    , C = max(case when c.RowNum = 2 then C end)
    , A = max(case when c.RowNum = 3 then A end)
    , B = max(case when c.RowNum = 3 then B end)
    , C = max(case when c.RowNum = 3 then C end)
from MyCte c
group by c.MyTime