| Transno | Date |
| 1223 | 11/01/2016 |
| 2795 | 12/01/2016 |
| Transno | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 1223 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2795 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
First I select transno, day of week as a number, and some constant for future pivotting.
@res1 =
SELECT Transno, Convert.ToInt32(Date.DayOfWeek) AS wd, 1 AS a1
FROM dbo.LocalTransno;
Then use MAP_AGG, which is a base for pivot:
@res2 =
SELECT Transno,
1 AS k1,
MAP_AGG(wd, (int?) a1) AS mapwd
FROM @res1
GROUP BY Transno;
Here I create a dummy table with 1 row, just to select values from 1 to 7 (my weekdays numbers) - as a key values for pivot.
@one = SELECT * FROM (VALUES(1)) AS T(a);
@keys =
SELECT
1 AS k1,
Inmeta.USQLScripts.Helper.InitList(1, 7) AS dkeys
FROM @one;
Where InitList s a code-behind function returning an object of SqlArray<int>:
public partial class Helper {
public static SqlArray<int> InitList(int lower, int upper) {
var values = new List<int>();
for (int i = lower; i <= upper; i++) {
values.Add(i);
}
var res = new SqlArray<int>(values) ;
return res;
}
}
And finally, pivotting and unrolling to comma-separated format at once:
@res =
SELECT
a.Transno.ToString() + "," +
String.Join(",", b.dkeys.ToList().Select(k => a.mapwd.ContainsKey(k) ? 1 : 0))
AS x
FROM @res2 AS a
JOIN @keys AS b ON a.k1 == b.k1;
Note the trick of dummy join, to connect weekdays keys table with my data :)
And finally output - removing the quotes from string output gives a clean csv :
OUTPUT @res
TO @out
USING Outputters.Csv(quoting : false);
It's a bit of running around, but that's the only way I managed to make it work.
