Tinyint convert to nvarchar with grouping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tinyint convert to nvarchar with grouping

Author

Topic

rcp
Starting Member

32 Posts

Posted - 2012-06-26 : 10:19:17

Hi,

I am trying to take several columns which are tinyint merge them into one column then do a count.

eg.

ER1 ER2 ER3 ER4 Id
0 1 1 0 0FEB939D-6331-4DED-9860-95D36B7F7828
0 1 1 0 0FEB939D-6331-4DED-9870-95D36B7F7828
0 1 1 0 0FEB939D-6331-4DED-9880-95D36B7F7828

required results

ERALL Count
0110 3

This is what I have go so far:

SELECT
CAST(CAST([er2001] as nvarchar (4000))
+ CAST([er2002] as nvarchar (4000))
+ CAST([er2003] as nvarchar (4000))
+ CAST([er2004] as nvarchar (4000))
+ CAST([er2005] as nvarchar (4000))
+ CAST([er2006] as nvarchar (4000))
+ CAST([er2007] as nvarchar (4000))
+ CAST([er2008] as nvarchar (4000))
+ CAST([er2009] as nvarchar (4000))
+ CAST([er2010] as nvarchar (4000))
+ CAST([er2011] as nvarchar (4000))
+ CAST([er2012] as nvarchar (4000)) AS text) AS ERALL
FROM [erdata_20120225].[dbo].[erdata] WITH (nolock)
where CAST([er2001] as nvarchar (4000))
+ CAST([er2002] as nvarchar (4000))
+ CAST([er2003] as nvarchar (4000))
+ CAST([er2004] as nvarchar (4000))
+ CAST([er2005] as nvarchar (4000))
+ CAST([er2006] as nvarchar (4000))
+ CAST([er2007] as nvarchar (4000))
+ CAST([er2008] as nvarchar (4000))
+ CAST([er2009] as nvarchar (4000))
+ CAST([er2010] as nvarchar (4000))
+ CAST([er2011] as nvarchar (4000))
+ CAST([er2012] as nvarchar (4000)) like '%1%0%1%'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-26 : 10:22:37

[code]SELECT ERALL, count(*)
FROM
(
SELECT
CAST([er2001] as nvarchar (5))
+ CAST([er2002] as nvarchar (5))
+ CAST([er2003] as nvarchar (5))
+ CAST([er2004] as nvarchar (5))
+ CAST([er2005] as nvarchar (5))
+ CAST([er2006] as nvarchar (5))
+ CAST([er2007] as nvarchar (5))
+ CAST([er2008] as nvarchar (5))
+ CAST([er2009] as nvarchar (5))
+ CAST([er2010] as nvarchar (5))
+ CAST([er2011] as nvarchar (5))
+ CAST([er2012] as nvarchar (5)) AS ERALL
FROM [erdata_20120225].[dbo].[erdata] WITH (nolock)
) as d
group by ERALL[/code]

KH
[spoiler]Time is always against us[/spoiler]

rcp
Starting Member

32 Posts

Posted - 2012-06-26 : 10:29:07

khtan, thank you very much.

Roland

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources