对于重复数据需要为空 [英] for Repeated data need blank

问题描述

HPT	     HPTU	COMPT	COMPTU
5/3/2012	5555	5/2/2012	392
5/3/2012	5555	5/2/2012	302
5/3/2012	5555	5/3/2012	406

这是我表中的数据，这里前两列重复相同的值，我需要查询以选择单行"，重复的值应为空或为空
喜欢

Here is data in my table, Here First two column repeating same values i need a query to select Single row and for repeated values should be blank or Null
like

HPT	     HPTU	COMPT	COMPTU
5/3/2012	5555	5/2/2012	392
	             	5/2/2012	302
		        5/3/2012	406

任何人都可以帮助我.

any one can help me.

推荐答案

如果您想在SQL命令中获得此功能，除非...您使用 PIVOT [ HPT HPTU COMPT COMPTU 5/3/2012 5555 5/2/2012 392 5/3/2012 5555 5/2/2012 302 5/3/2012 5555 5/3/2012 406
将显示如下:

HPT	HPTU	COMPT	392	302	406
5/3/2012	5555	5/2/2012	1	1	1

其中COMPTU转到392、302、406

如何实现?

If you would like to get this functionallity in SQL command, that''s not possible, unless... you use a PIVOT[^].

Using PIVOT, your data

HPT	HPTU	COMPT	COMPTU
5/3/2012	5555	5/2/2012	392
5/3/2012	5555	5/2/2012	302
5/3/2012	5555	5/3/2012	406

will be presented as follow:

HPT	HPTU	COMPT	392	302	406
5/3/2012	5555	5/2/2012	1	1	1

where COMPTU goes to 392, 302, 406

How to achieve that?

DECLARE @cols NVARCHAR(200)
DECLARE @ptqry NVARCHAR(1000)
DECLARE @dtqry NVARCHAR(2000)

--dynamic columns from [COMPTU] values
SET @cols = STUFF((SELECT DISTINCT '],[' + [COMPTU]
					FROM [YourTable]
					ORDER BY '],[' + [COMPTU]
			FOR XML PATH('')),1,2,'') + ']'

@dtqry = 'SELECT [HPT], [HPTU], [COMPT], [COMPTU] ' + 
        'FROM [YourTable]'
--EXEC(@dtqry)

@ptqry = 'SELECT [HPT], [HPTU], [COMPT], ' + @cols + ' ' + 
        'FROM (' + @dtqry + ') AS DT ' +
        'PIVOT (COUNT([COMPTU]) FOR [HPTU] IN(' + @cols + ')) AS PT ' + 
        'ORDER PT.[HPT]'
EXEC(@ptqry)

任何其他配置都是可能的;)

更多:如何在数据表中合并相似的行(行仅相差一列) [ ^ ]
需要执行某些操作的sql查询:根据其他列将行分为一列 [ ^ ]

Any other configuration is possible ;)

More: http://webcache.googleusercontent.com/search?q=cache:QQ59atWRpVkJ:http://msdn.microsoft.com/en-us/library/ms177410.aspx%2BT-SQL%2BPIVOT&hl=pl&output=search&sclient=psy-ab&gbv=1&ct=clnk[^]

You can merge some data into one row.
How to combine similar rows in a Datatable(the rows only differ by one column)?[^]
need sql query for some action: rows into single column depending on other column[^]

签出取消双精度字段名 [ ^ ]

这篇关于对于重复数据需要为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！