SQL Server: 显示每个数据集的列

huangapple
go评论84阅读模式
英文:

SQL Server : display column with each data set

问题

  1. Description = North Entry,
  2. MessageDescription = AccessGrantedNoEntry,
  3. CardNumber = 0,
  4. TimeStamp_Server = 2023-02-08,
  5. CardHolderID = Retail Center C004 Converted PXL250-2DOOR,
  6. FirstName = ,
  7. MiddleName = ,
  8. LastName = ,
  9. CardStatusID = ,
  10. CardStatusDescription = ,
  11. Imprint = ,
  12. TransactionNumber = 527312
英文:

I'm exporting data out to a flat file to import into a SIEM. Is there a way to display as "column name = data" for each item?

  1. SELECT 
  2.     [Description], [MessageDescription], [CardNumber], 
  3.     [TimeStamp_Server], [SPMDescription] [CardHolderID], 
  4.     [FirstName], [MiddleName], [LastName],
  5.     [CardStatusID], [CardStatusDescription], [Imprint],
  6.     [TransactionNumber]
  7. FROM 
  8.     [DB].[dbo].[Message]
  9. WHERE 
  10.     CONVERT(varchar(10), TimeStamp_Server, 120) = CONVERT(varchar(10), GETDATE(), 120)

Here is how it currently presents in the flat file.

  1. Description,MessageDescription,CardNumber,TimeStamp_Server,CardHolderID,FirstName,MiddleName,LastName,CardStatusID,CardStatusDescription,Imprint,TransactionNumber
  2. North Entry,AccessGrantedNoEntry,0,2023-02-08 09:52:19,Retail Center C004 Converted PXL250-2DOOR,,,,,,,527312

I'd like it to display as this for each row

  1. Description = North Entry,
  2. MessageDescription = AccessGrantedNoEntry,
  3. CardNumber = 0, 
  4. TimeStamp_Server = 2023-02-08

... and so on.

答案1

得分: 1

这是一个侧面问题（因此是社区维基），但您可以通过像这样更改WHERE子句来显著改善此查询的性能（假设TimeStamp_Server是DateTime列）：

  1. WHERE TimeStamp_Server >= cast(cast(getdate() as date) as datetime) 
  2.     AND TimeStamp_Server < cast(dateadd(day, 1, cast(getdate() as date)) as datetime)

这有三种帮助：

  1. 由于文化/国际化问题，将日期转换为字符串值并从字符串值转换日期比我们想象中要慢得多，也更容易出错。坚持使用日期函数和类型将始终性能更好且更准确。
  2. 通过将所有修改都移到getdate()上，使TimeStamp_Server保持不变，我们避免了需要在表中的每一行上执行转换。
  3. 通过将所有修改都移到getdate()上，使TimeStamp_Server保持不变，我们保留了该列可能存在的任何索引的使用。这对于数据库性能至关重要。
英文:

This is a side issue (so community wiki), but you can significantly improve performance of this query by changing the WHERE clause like this (assuming TimeStamp_Server is a DateTime column):

  1. WHERE TimeStamp_Server &gt;= cast(cast(getdate() as date) as datetime) 
  2.     AND TimeStamp_Server &lt; cast(dateadd(day, 1, cast(getdate() as date)) as datetime)

This helps in three ways:

  1. Thanks to cultural/internationalization issues, converting dates to and from string values is far slower and more error-prone than we'd like to believe. Sticking with Date functions and types will always perform better and be more accurate.
  2. By shifting all the modifications to getdate(), so TimeStamp_Server is unaltered, we avoid needing to do the conversion on every row in the table.
  3. By shifting all the modifications to getdate(), so TimeStamp_Server is unaltered, we preserve the use of any index that might exist for the column. This cuts to the core of database performance.

答案2

得分: 1

使用一点JSON和 string_agg()

  1. Select B.NewVal
  2. From ( 
  3.        --在此处插入您的查询-- 
  4.      ) A
  5.  Cross Apply (
  6.                 Select NewVal = string_agg(concat([key],' = ',value),',')
  7. 				 From openjson( (Select A.* For JSON Path,Without_Array_Wrapper  ) )
  8.               ) B
英文:

With a bit of JSON and string_agg()

  1. Select B.NewVal
  2. From ( 
  3.        --Your Query Here-- 
  4.      ) A
  5.  Cross Apply (
  6.                 Select NewVal = string_agg(concat([key],&#39; = &#39;,value),&#39;,&#39;)
  7. 				 From openjson( (Select A.* For JSON Path,Without_Array_Wrapper  ) )
  8.               ) B

答案3

得分: 0

抱歉，你没有提供一个最小可重现的示例。所以，我只能凭直觉提供以下概念示例。

请尝试以下概念示例。

在我看来，JSON或XML输出格式会更可靠。

SQL

  1. -- DDL和示例数据填充，开始
  2. DECLARE @tbl TABLE (id INT PRIMARY KEY, case_date DATE, Product INT);
  3. INSERT INTO @tbl (id, case_date, Product) VALUES
  4. (55, '2022-08-01', 11),
  5. (66, '2022-05-21', 51);
  6. -- DDL和示例数据填充，结束
  8. SELECT t.*
  9. 	, result = STUFF(XMLData.query('
  10. 		for $x in /root/*
  11. 		return concat(", ", local-name($x), "=", $x/text()[1])
  12. 		').value('.', 'VARCHAR(4096)'), 1, 2, '')
  13. FROM @tbl AS t
  14. CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(XMLData);

输出

id case_date Product result
55 2022-08-01 11 id=55, case_date=2022-08-01, Product=11
66 2022-05-21 51 id=66, case_date=2022-05-21, Product=51
英文:

A minimal reproducible example is not provided. So, I am shooting from the hip.

Please try the following conceptual example.

IMHO, the JSON or XML output format would be much more reliable.

SQL

  1. -- DDL and sample data population, start
  2. DECLARE @tbl TABLE (id INT PRIMARY KEY, case_date DATE, Product INT);
  3. INSERT INTO @tbl (id, case_date, Product) VALUES
  4. (55, &#39;2022-08-01&#39;, 11),
  5. (66, &#39;2022-05-21&#39;, 51);
  6. -- DDL and sample data population, end
  8. SELECT t.*
  9. 	, result = STUFF(XMLData.query(&#39;
  10. 		for $x in /root/*
  11. 		return concat(&quot;, &quot;, local-name($x), &quot;=&quot;, $x/text()[1])
  12. 		&#39;).value(&#39;.&#39;, &#39;VARCHAR(4096)&#39;), 1,2,&#39;&#39;)
  13. FROM @tbl AS t
  14. CROSS APPLY (SELECT t.* FOR XML PATH(&#39;&#39;), TYPE, ROOT(&#39;root&#39;)) AS t1(XMLData);

Output

id case_date Product result
55 2022-08-01 11 id=55 , case_date=2022-08-01 , Product=11
66 2022-05-21 51 id=66 , case_date=2022-05-21 , Product=51

答案4

得分: 0

谢谢 @Stu 为我提供了我所寻求的内容 在评论中:

所以你想要在每一行上复制每个列名吗？这将显著增加文件的大小！只需使用

  1. select concat('columnName = ', ColumnName)Columename, ...
英文:

Thank you @Stu for providing what I was seeking in a comment:

> So you want every column name reproduced on every row? That's going to significantly increase the size of the file! Just use
>
&gt; select concat(&#39;columnName = &#39;, ColumnName)Columename, ...
&gt;

huangapple
  • 本文由 发表于 2023年2月9日 01:27:26
  • 转载请务必保留本文链接：https://go.coder-hub.com/75389556.html
  • sql-server
  • t-sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定