Unusual output format needed in SQL

Question

I have a sql table in the format;

Name Date Value ABC 1/21/2015 52 ABC 1/22/2015 12 ABC 1/23/2015 32 DEF 1/21/2015 78 DEF 1/22/2015 53 etc... 

For compatibility with a legacy program, I need to format a text file as follows:

 ABC 1/21/2015,52 1/22/2015,12 1/23/2015,32 DEF 1/21/2015,78 1/22/2015,53 

Any suggestions would be helpful.

Answer 1

One option is to create header lines for the result set using UNION , and then sort them so that they appear at the top of each Name group. You can do this using a computed column that identifies these generated rows as headers.

 SELECT CASE WHEN t.Header = 1 THEN t.Name ELSE CONCAT(t.Date, ',', t.Value) END FROM ( SELECT DISTINCT Name, NULL AS Date, NULL AS Value, 1 AS Header FROM yourTable UNION ALL SELECT Name, Date, Value, 0 AS Header FROM yourTable ) t ORDER BY t.Name, t.Header DESC, t.Date 

Answer 2

This is pretty hacky, but the result is what you need:

The trick is to use intermediate XML in this form:

 <x>ABC</x> <x>2015.01.21,52</x> <x>2015.01.22,12</x> <x>2015.01.23,32</x> <x>DEF</x> <x>2015.01.21,78</x> <x>2015.01.22,53</x> 

Then it’s easy to read each “node”:

 SET LANGUAGE ENGLISH; DECLARE @tbl TABLE(Name VARCHAR(100),Date DATE,Value INT); INSERT INTO @tbl VALUES ('ABC','1/21/2015',52) ,('ABC','1/22/2015',12) ,('ABC','1/23/2015',32) ,('DEF','1/21/2015',78) ,('DEF','1/22/2015',53); WITH DistinctNames AS ( SELECT DISTINCT NAME FROM @tbl ) SELECT One.Line.value('.','varchar(100)') AS OneLine FROM ( SELECT ( SELECT Name AS [x] ,( SELECT CONVERT(VARCHAR(10),t.Date,102) + ',' + CAST(t.Value AS VARCHAR(100)) FROM @tbl AS t WHERE t.Name=DistinctNames.Name FOR XML PATH('x'),TYPE ) FROM DistinctNames FOR XML PATH(''),TYPE ) ) AS tbl(x) CROSS APPLY x.nodes('/x') AS One(Line) 

Answer 3

The easiest way to solve this for me is to use a query.

 SELECT Name, Date, Value FROM Table ORDER BY Name ASC, Date ASC 

And then use a short PHP script that each time it gets a new name, echoes it and discards all lines until a new name is found.

 <?php // SQL $name = ""; foreach ($response as &$line) { if ($name != $line['Name']) { $line['Name'] = $name; echo $name . "<br />"; } echo $line['Date'] . "," . $line['Value'] . "<br/>"; } ?> 

And save it in a text file.

Note. You can use the "million" of different languages. I just wrote in PHP, because for me it would be the most accessible. My idea would be the same for others.