Вы можете попробовать это. PIVOT и некоторые оконные функции могут решить вашу проблему.
SELECT
StartDate,
EndDate,
ID,
ISNULL([Name], FIRST_VALUE([Name]) OVER(PARTITION BY ID ORDER BY StartDate)) AS [Name],
ISNULL([Status], FIRST_VALUE([Status]) OVER(PARTITION BY ID ORDER BY StartDate)) AS [Status],
ISNULL([Group], FIRST_VALUE([Group]) OVER(PARTITION BY ID ORDER BY StartDate)) AS [Group],
ISNULL([Sub Group], FIRST_VALUE([Sub Group]) OVER(PARTITION BY ID ORDER BY StartDate))AS [Sub Group]
FROM (
SELECT StartDate,
ISNULL(EndDate, MAX(EndDate) OVER(PARTITION BY StartDate,ID )) AS EndDate,
ID, Dimension, Value
FROM MyTable
) SRC
PIVOT(MAX( Value) FOR Dimension IN ([Name], [Status], [Group], [Sub Group])) PVT
order by ID desc, StartDate
Динамическая версия:
DECLARE @Columns NVARCHAR(MAX) =''
DECLARE @PivotIn NVARCHAR(MAX) =''
SELECT
@PivotIn = CONCAT(@PivotIn ,', ', QUOTENAME(Dimension))
, @Columns = CONCAT(@Columns , ', ', 'ISNULL(',QUOTENAME(Dimension),', FIRST_VALUE(',QUOTENAME(Dimension),') OVER(PARTITION BY ID ORDER BY StartDate)) AS ',QUOTENAME(Dimension),'')
FROM ( SELECT DISTINCT Dimension FROM MyTable ) AS X
DECLARE @SqlQuery NVARCHAR(MAX) = 'SELECT
StartDate,
EndDate,
ID ' +
@Columns
+' FROM
(
SELECT StartDate,
ISNULL(EndDate, MAX(EndDate) OVER(PARTITION BY StartDate,ID )) AS EndDate,
ID, Dimension, Value
FROM MyTable
) SRC
PIVOT(MAX( Value) FOR Dimension IN (' + STUFF(@PivotIn,1,1,'') + ')) PVT
ORDER BY ID DESC, StartDate'
EXEC sp_executesql @SqlQuery
Это должно работать:
header('Content-type: text/plain');
header('Content-disposition: attachment; filename="test.txt"');
Просто чтобы расширить ответ @Emil H:
Использование этих вызовов заголовка будет работать только в контексте Новый запрос. Вам нужно будет реализовать что-то, что позволит вашему сценарию знать, когда он на самом деле отправляет файл, в отличие от отображения формы, сообщающей пользователю о загрузке файла.
To clarify the usage of header():
header() is used to send a raw HTTP header. See the » HTTP/1.1 specification for more information on HTTP headers.
Remember that header() must be called before any actual output is sent, either by normal HTML tags, blank lines in a file, or from PHP. It is a very common error to read code with include(), or require(), functions, or another file access function, and have spaces or empty lines that are output before header() is called. The same problem exists when using a single PHP/HTML file.
So basically, you're changing the entire page when you're using header(). Make sure the only contents you echo are the string.
<?
header ("Content-Type: application/download");
header ("Content-Disposition: attachment; filename=$yourfile");
header("Content-Length: " . filesize("$yourfile"));
$fp = fopen("$yourfile", "r");
fpassthru($fp);
?>