DECLARE @Query varchar(8000)

Create Table 
#tempCountRichard (Status varchar(50), Number Varchar(1000)

Set @Query = 'Insert Into #tempCountRichard
                  Select Count(Status),
                  dbo.GetStatusState(status) As Status
                  From asset
                  Where deleted = 0
                  Group By Status'

EXEC (@Query)

                  Select Status, dbo.GetStatusState(status)
                  From #tempCountRichard
                  Group by Status

With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.

Count    Status
1010
2000
1111
2222

When I run the query that is being inserted into the the temp table I return the correct results

Count    Status
1010     Pass
2000     Pass with Obs
1111     Fail
2222     None

I need to be able to understand how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.

All help always highly regarded...

Thanks, R

DECLARE @Query varchar(8000)

Create Table 
#tempCountRichard (Status varchar(50), Number Varchar(1000)

Set @Query = 'Insert Into #tempCountRichard
                  Select Count(Status),
                  dbo.GetStatusState(status) As Status
                  From asset
                  Where deleted = 0
                  Group By Status'

EXEC (@Query)

                  Select Status, dbo.GetStatusState(status)
                  From #tempCountRichard
                  Group by Status

With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.

Count    Status
1010
2000
1111
2222

When I run the query that is being inserted into the the temp table I return the correct results

Count    Status
1010     Pass
2000     Pass with Obs
1111     Fail
2222     None

I need to be able to understand how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.

All help always highly regarded...

Thanks, R

check the select query after the inserting the data into #temp table..

you are gettting count(status) as status and you are passing that value as parameter to the function - which infact you are doing in the insert statement itself.. so, you select should really be

Select status,number
 From #tempCountRichard
 

and this should match with 

Select Count(Status),
                  dbo.GetStatusState(status) As Status
                  From asset
                  Where deleted = 0
                  Group By Status

In other words

Select Status, dbo.GetStatusState(status)
                  From #tempCountRichard
                  Group by Status

here - status column in temp table is actually count(status) and then you are passing that value - to function which is not status but count(status)..that's the reason.. 

Hi,

I totally agree with stan.

But I just wonder why you ever want that simple insert statement to be executed dynamically?

Also, result of Count(status) is always integer, but in the create table statement it's of Varchar data type.

Yes there is more to the stored proc, as it does return an int value but the function returns the string text value of the ints.

If I want to return values from my set paratmeter in the same select

Declare @Colour varchar(8000)
Set @Colour = 'Purple, Blue, Brown, Black'

Select status,number
From #tempCountRichard 

Select value from dbo.fn_udfSplit(@Color, ',')

How would I nest it all into one query ?

Thanks R

Yes there is more to the stored proc, as it does return an int value but the function returns the string text value of the ints.

If I want to return values from my set paratmeter in the same select

Declare @Colour varchar(8000)
Set @Colour = 'Purple, Blue, Brown, Black'

Select status,number
From #tempCountRichard 

Select value from dbo.fn_udfSplit(@Color, ',')

How would I nest it all into one query ?

Thanks R

I do not see any join condition and if you really need to join - your best best is to do cross join

how are you joinging in your store procedure the two selects?...

declare @table1 table(sno int)
insert into @table1 values(1),(2)

declare @table2 table(sname varchar(20))
insert into @table2 values('adam'),('winley')

select * from @table1 cross join @table2 

so, it may be something like

Declare @Colour varchar(8000)
Set @Colour = 'Purple, Blue, Brown, Black'

Select status,number,value
From #tempCountRichard cross join dbo.fn_udfSplit(@Color, ',')

Hi,

The Cross join returns 16 rows where I need 4. Count, Status, Color. I was trying with a left outer join. But both are returning incorrect results.

Without using a join how can I return the following dataset from my query ? As I can return Count and Status just not match the color?

Count  Status  Colour
1110   Pass    Purple
1122   PWO     Blue
1133   Fail    Brown
1122   None    Black

Declare @query varchar (8000)
Declare @Color varchar (8000)
Set @Color = (Purple, Blue, Brown, Black)

Create Table #tempCountRichard(Status Varchar(50), Number Varchar(50) Colour Varchar(50))

Set @Query = ' Insert Into #tempCountRichard
               Select 
                    Count(Status)
                    dbo.GetStatusState(status)
               From asset
               Where deleted = 0
               Group By Status '

EXEC(@Query)

Select Number, Status, Color
From #tempCountRichard 

I have a function dbo.fn_udfSplit that splits the string for me but where can I utilize in the code ??

Thanks, R

I've read your posts, but I'm afraid that I'm not really able to make out what you are looking for.

Can you post:

1) CREATE TABLE statements for your table(s).
2) INSERT statements with sample data.
3) The desired result given the sample.
4) A short description of the business rules.
5) Which version of SQL Server you are using.

This helps to clarify the problem. Also, it makes it simple to copy and paste into a query window to develop a tested solution.

Hi Erland,

Thank you for your response and help.

1.  I can't provide the whole schema sorry... I hope you understand .

Create Table #tempCountRichard(Status Varchar(50), Number Varchar(50))

Set @Query = ' Insert Into #tempCountRichard
               Select 
                    Count(Status)
                    dbo.GetStatusState(status)
               From asset
               Where deleted = 0
               Group By Status '

EXEC(@Query)

Select Number, Status
From #tempCountRichard 

 

Above query returns the correct dataset from the table's you have asked for.

Count    Status
1010     Pass
2000     Pass with Obs
1111     Fail
2222     None

What I need to do is add an extra field in the result set; That has been Set as a parameter and isn't returned from the database.

Declare @Color varchar (8000)
Set @Color = (Purple, Blue, Brown, Black)

There is also a function that will and does the split on the string and returns the value

Select value from dbo.fn_udfSplit(@Color, ',')

3. The desired result set would just be adding the field color - like so

Count  Status  Color
1110   Pass    Purple
1122   PWO     Blue
1133   Fail    Brown
1122   None    Black

 

4. Need a count of all the assets with its current inspection status. (Done). A String parameter needs to be set in the code and with the above function can be split and returned alongside data retrieved from the database.

5. SQL 2008r2.

Once again Erland thanks for the help, I would provide the tables and insert if I could but I can't provide that bit sorry. The data returning from that db is correct its just adding the Set parameter in the code to the result !! 

Thanks, Richard Evans

• Edited by Richard I Evans 12 hours 54 minutes ago

Hi,

The Cross join returns 16 rows where I need 4. Count, Status, Color. I was trying with a left outer join. But both are returning incorrect results.

Without using a join how can I return the following dataset from my query ? As I can return Count and Status just not match the color?

Count  Status  Colour
1110   Pass    Purple
1122   PWO     Blue
1133   Fail    Brown
1122   None    Black

Declare @query varchar (8000)
Declare @Color varchar (8000)
Set @Color = (Purple, Blue, Brown, Black)

Create Table #tempCountRichard(Status Varchar(50), Number Varchar(50) Colour Varchar(50))

Set @Query = ' Insert Into #tempCountRichard
               Select 
                    Count(Status)
                    dbo.GetStatusState(status)
               From asset
               Where deleted = 0
               Group By Status '

EXEC(@Query)

Select Number, Status, Color
From #tempCountRichard 

I have a function dbo.fn_udfSplit that splits the string for me but where can I utilize in the code ??

Thanks, R

Richard - you do not have any condition and so, cross join was the closest bet but you did not post your excepted output intially. anyways, i advise you to study SQL join types for better understanding. cross join is typically multiplication of rows in both the table and hence 16 rows in your output.

coming to your question on excepted output, you still do not join condition. you can hard code and make it a join condition or simply write a case statement - that will give you the same output...

something like this 

1.  I can't provide the whole schema sorry... I hope you understand .

We only see as much that is needed to illustrate the problem; I forgot to add that note. In this case it would be the table asset. It would also help to see the code of dbo.GetStatusState.

3. The desired result set would just be adding the field color - like so

Count  Status  Color
1110   Pass    Purple
1122   PWO     Blue
1133   Fail    Brown
1122   None    Black
 

And what is the business rules that says that 1110 is Purple, 1122 Blue etc? Hm, wait the next 1122 is Black? Sorry, you have lost me entirely.

****** Object:  Table [dbo].[asset]    Script Date: 07/11/2015 20:03:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[asset](
	[uid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[name] [nvarchar](30) NOT NULL,
	[typeid] [uniqueidentifier] NOT NULL,
	[tagid] [nvarchar](30) NULL,
	[status] [int] NOT NULL,
	[state] [int] NOT NULL,
	[locationid] [uniqueidentifier] NULL,
	[locked] [bit] NOT NULL,
	[parentid] [uniqueidentifier] NULL,
	[deleted] [bit] NOT NULL,
	[GpsLatitude] [float] NULL,
	[GpsLongitude] [float] NULL,
 CONSTRAINT [PK_asset] PRIMARY KEY CLUSTERED 
(
	[uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[asset]  WITH NOCHECK ADD  CONSTRAINT [FK_asset_location] FOREIGN KEY([locationid])
REFERENCES [dbo].[location] ([uid])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[asset] NOCHECK CONSTRAINT [FK_asset_location]
GO

ALTER TABLE [dbo].[asset]  WITH NOCHECK ADD  CONSTRAINT [FK_asset_type] FOREIGN KEY([typeid])
REFERENCES [dbo].[type] ([uid])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[asset] NOCHECK CONSTRAINT [FK_asset_type]
GO

ALTER TABLE [dbo].[asset] ADD  CONSTRAINT [DF_asset_uid]  DEFAULT (newid()) FOR [uid]
GO

ALTER FUNCTION [dbo].[GetStatusState]
	(
		@State 	 int
	)
RETURNS varchar(255) /* datatype */
AS
	BEGIN
	/* sql statement ... */
	    declare @name as varchar(255)
	    declare @UID as uniqueidentifier 
	    
	IF @State < 10
	Begin     
	 set @UID = '00000000-0000-0000-0000-00000000000' + cast(@State as varchar)
	End
	
	IF @State >9 
	Begin
	   set @UID ='00000000-0000-0000-0000-0000000000' + cast(@State as varchar)
    End


	    select @name=[name] from lookup where uid =@UID
	   
	   IF @name is null 
	   Begin
	   		set @name=''
	   End 
	 		
	RETURN  @name /* value */
	END

Asset table and the function.

The business rule doesn't matter as the colors need to be hardcoded in the query and return with result set.

The count vary from database to database one company could 30,000 assets another could have 6 i just put numbers in there to give a visul representation of what it should look like!!!

The function looks at a lookup table that stores the text values of the status and matches the UID. I feel No changes should be made to the function or the table. Thanks 

The business rule doesn't matter as the colors need to be hardcoded in the

query and return with result set.

OK if the rules don't matter I guess anything goes:

INSERT @colours (id, colour)
   SELECT row_number() OVER(ORDER BY (SELECT newid())) - 1, value
   FROM   dbo.fn.udfSplit(@Color)

Select Number, Status,
       (SELECT colour
        FROM   @colours
        WHERE  id = abs(checksum(newid()) % (SELECT COUNT(*) FROM @colours)
From #tempCountRichard

Interesting.....

DECLARE @Colour varchar (8000) = 'Purple, Blue, Brown, Black'


Create Table #Colours(id int, Colour Varchar(8000))
Set @Colour = 'Insert Into #Colours (id, Colour)
               Select row_number() OVER(ORDER BY (SELECT newid())) - 1, value 
               From dbo.fn_udfSplit(''' +@Colour+ ''','','')'
               EXEC (@Colour)
               
               Select Colour from #Colours
               Drop table #Colours

Returns

DECLARE @Query  varchar (8000)
DECLARE @Colour varchar (8000) = 'Purple, Blue, Brown, Black'


Create Table #Colours(id int, Colour Varchar(8000))
Set @Colour = 'Insert Into #Colours (id, Colour)
               Select row_number() OVER(ORDER BY (SELECT newid())) - 1, value 
               From dbo.fn_udfSplit(''' +@Colour+ ''','','')'
               EXEC (@Colour)

CREATE TABLE #tempCountRichard(Status VARCHAR (50),[Number] VARCHAR(1000))
			
	        
	        SET @Query= ' INSERT INTO #tempCountRichard
			Select 
			       COUNT(status), 
                   dbo.GetStatusState(status) Status
                   FROM asset
			       WHERE deleted = 0
			       Group By status'
			 EXEC (@Query)
			 
			 Select Number, Status,
			 (Select Colour from #Colours
			  where id = ABS(checksum(newid())%(Select Count(*) From #Colours)))As Colour
			 from #tempCountRichard 
			 
			 Drop Table #tempCountRichard, #Colours
			 

The condition is in place and its counting on the newid would it need a cast ??

Hi Erland,

Thank you for your response and help.

1.  I can't provide the whole schema sorry... I hope you understand .

Create Table #tempCountRichard(Status Varchar(50), Number Varchar(50))

Set @Query = ' Insert Into #tempCountRichard
               Select 
                    Count(Status)
                    dbo.GetStatusState(status)
               From asset
               Where deleted = 0
               Group By Status '

EXEC(@Query)

Select Number, Status
From #tempCountRichard 

 

Above query returns the correct dataset from the table's you have asked for.

Count    Status
1010     Pass
2000     Pass with Obs
1111     Fail
2222     None

What I need to do is add an extra field in the result set; That has been Set as a parameter and isn't returned from the database.

Declare @Color varchar (8000)
Set @Color = (Purple, Blue, Brown, Black)

There is also a function that will and does the split on the string and returns the value

Select value from dbo.fn_udfSplit(@Color, ',')

3. The desired result set would just be adding the field color - like so

Count  Status  Color
1110   Pass    Purple
1122   PWO     Blue
1133   Fail    Brown
1122   None    Black

 

4. Need a count of all the assets with its current inspection status. (Done). A String parameter needs to be set in the code and with the above function can be split and returned alongside data retrieved from the database.

5. SQL 2008r2.

Once again Erland thanks for the help, I would provide the tables and insert if I could but I can't provide that bit sorry. The data returning from that db is correct its just adding the Set parameter in the code to the result !! 

Thanks, Richard Evans

• Edited by Richard I Evans Saturday, July 11, 2015 6:11 PM

I have seperated the code so I return the hardcode string through dynamic sql but I don't return the colors I have defined in our parameter, I see a new row_number is created and is detrmined by the newid it creates. It still returns blank.

I don't know what is returned blank, but this

Create Table #Colours(Colour Varchar(50))
Set @Colour = 'Insert @colours(id, colour)
               Select ROW_NUMBER() over(Order By (Select newid()))-1 Value
               From dbo.fn_udfSplit(''' +@Colour+ ''','','')'

               Select Colour from #Colours
               Drop table #Colours

Should be

DECLARE @Colour varchar (8000) = 'Purple, Blue, Brown, Black'

Create Table #Colours(ID int NOT NULL PRIMARY KEY,
                      Colour Varchar(50) NOT NULL)
Set @ColourSQL = 'Insert #colours(id, colour)
               Select ROW_NUMBER() over(Order By (Select newid()))-1 Value
               From dbo.fn_udfSplit(@Colour)'
EXEC sp_executesql @ColourSQL, N'@Colour varchar(8000)', @Colour

               Select Colour from #Colours
               Drop table #Colours

Never interpolate values into SQL strings when you can pass them by parameters.

Of course, there is also the question why you use dynamic SQL at all. That's another thing you have not explained.

Create Table #Colours(id int, Colour Varchar(8000))
Set @Colour = 'Insert Into #Colours (id, Colour)
               Select row_number() OVER(ORDER BY (SELECT newid())) - 1, value
               From dbo.fn_udfSplit(''' +@Colour+ ''','','')'
               EXEC (@Colour)

I told you not to do this. First, of all, don't use dynamic SQL, if there is no need to, and there isn't. And if you do, don't interpolate values into the SQL string, but pass them as parameters.

I did not say this for fun. This is about SQL injection, one of the most common ways to crack web site. (And please don't tell me that you are not working with a web site. This is something that should be at the core of your professional skill. I don't want to tell you again.)

                 where id = ABS(checksum(newid())%(Select Count(*) From #Colours)))As Colour

I had a missing right parentheses in my post. It should be

  abs(checksum(newid())) % (SELECT COUNT(*) FROM #Colours)

That is generate a random unsigned number and with the mod operation reduce it to a number between 1 and 4.

where id = ABS(checksum(newid())%(Select Count(*) From #Colours)))As Colour

I had a missing right parentheses in my post. It should be

  abs(checksum(newid())) % (SELECT COUNT(*) FROM #Colours)

That is generate a random unsigned number and with the mod operation reduce it to a number between 1 and 4.

Okay, I see when you generate the random unsigned number can it be assigned so Purple = 1, Blue = 2, Brown = 3 and Black = 1. As when I return the query. I get this. (No more Dynamic I will say)

DECLARE @ColourSQL varchar (1000) = 'Purple, Blue, Brown, Black'

            Create Table #Colours(ID int NOT NULL PRIMARY KEY, 
                      Colour Varchar(50) NOT NULL)
               Insert into #colours(id, colour)
               Select ROW_NUMBER() over(Order By (Select newid()))-1, Value
               From dbo.fn_udfSplit(@ColourSQL, ',')
			
			
			CREATE TABLE #tempCountRichard(Status VARCHAR (50),[Number] VARCHAR(1000))
			
	        Insert Into #tempCountRichard 
	         Select COUNT(status), 
                   dbo.GetStatusState(status) Status
                   FROM asset
			       WHERE deleted = 0
			       Group By status
	
			 
			 Select Number, Status,
			 (Select Colour from #Colours
			 where id =  abs(checksum(newid())) % (SELECT COUNT(*) FROM #Colours))
			 from #tempCountRichard 
			 Drop table #tempCountRichard, #Colours

Wrong.

It will be

Count  Status   Color
369      Pass      Purple
20        PWO     Blue
26        Fail        Brown
403      None     Black

Thanks

 

• Edited by Richard I Evans 7 hours 6 minutes ago

where id = ABS(checksum(newid())%(Select Count(*) From #Colours)))As Colour

I had a missing right parentheses in my post. It should be

  abs(checksum(newid())) % (SELECT COUNT(*) FROM #Colours)

That is generate a random unsigned number and with the mod operation reduce it to a number between 1 and 4.

Okay, I see when you generate the random unsigned number can it be assigned so Purple = 1, Blue = 2, Brown = 3 and Black = 1. As when I return the query. I get this. (No more Dynamic I will say)

DECLARE @ColourSQL varchar (1000) = 'Purple, Blue, Brown, Black'

            Create Table #Colours(ID int NOT NULL PRIMARY KEY, 
                      Colour Varchar(50) NOT NULL)
               Insert into #colours(id, colour)
               Select ROW_NUMBER() over(Order By (Select newid()))-1, Value
               From dbo.fn_udfSplit(@ColourSQL, ',')
			
			
			CREATE TABLE #tempCountRichard(Status VARCHAR (50),[Number] VARCHAR(1000))
			
	        Insert Into #tempCountRichard 
	         Select COUNT(status), 
                   dbo.GetStatusState(status) Status
                   FROM asset
			       WHERE deleted = 0
			       Group By status
	
			 
			 Select Number, Status,
			 (Select Colour from #Colours
			 where id =  abs(checksum(newid())) % (SELECT COUNT(*) FROM #Colours))
			 from #tempCountRichard 
			 Drop table #tempCountRichard, #Colours

Wrong.

It will be

Count  Status   Color
369      Pass      Purple
20        PWO     Blue
26        Fail        Brown
403      None     Black

Thanks

 

• Edited by Richard I Evans Saturday, July 11, 2015 11:59 PM

Since the subquery

                (Select Colour from #Colours
                where id =  abs(checksum(newid())) % (SELECT COUNT(*) FROM #Colours))

is uncorrelated with the surrounding query, the optimizers decided to evaluate it only once, why all get the same colour. Normally newid() is evaluated once per row, but I overlooked this situation.

Anyway, it may be better to do it this way:

  ; WITH CTE AS (
     SELECT Number, Status, row_number() OVER(ORDER BY newid()) AS rowno
     FROM   #tempCountRichard
  )
  SELECT CTE.Number, CTE.Status, c.Colour
  FROM   CTE
  JOIN   #Colours c ON CTE.rowno % 4 = c.id

Anyway, it may be better to do it this way:

  ; WITH CTE AS (
     SELECT Number, Status, row_number() OVER(ORDER BY newid()) AS rowno
     FROM   #tempCountRichard
  )
  SELECT CTE.Number, CTE.Status, c.Colour
  FROM   CTE
  JOIN   #Colours c ON CTE.rowno % 4 = c.id

With the following query in mind.

DECLARE @ColourSQL varchar (8000) = 'Purple, Blue, Brown, Black'

		
	/************************************************************************************/
	
	/********** Create Table to hold data ***********************************************/

            Create Table #Colours(ID int NOT NULL PRIMARY KEY, 
                      Colour Varchar(50) NOT NULL)
            Insert into #colours(id, colour)
            Select ROW_NUMBER() over(Order By (Select newid()))-1, 
            Value
            From dbo.fn_udfSplit(@ColourSQL, ',')
			
			
			CREATE TABLE #tempCountRichard(Status VARCHAR (50),[Number] VARCHAR(1000))
			
	        Insert Into #tempCountRichard 
	        Select COUNT(status), dbo.GetStatusState(status) Status
            FROM asset
			WHERE deleted = 0
			Group By status
	
			;WITH CTE AS (
			
			Select Number, Status, ROW_NUMBER()OVER(ORDER BY newid()) as rownum
			from #tempCountRichard 
			)
			SELECT CTE.Number, CTE.Status, c.Colour
			from CTE
			JOIN #Colours c on CTE.rownum % 4 = c.ID
			
			
			Drop table #tempCountRichard, #Colours

 

We return all required data from this, but the business logic requires it be in order of the string declared in our parameter.

But I can see there is no correlation between the status and and the Colour in our parameter.

When I excute the query this will change around. In the following,

 Select COUNT(status), dbo.GetStatusState(status) Status
            FROM asset
			WHERE deleted = 0
			Group By status

I have group by the status and retrun the corrcet results in the right order.

How can I set it so

Pass = Purple, Pass With Observation = Blue, Fail = Brown, None = Black and it doesn't change each time I excute.

Thanks.

• Edited by Richard I Evans 18 hours 42 minutes ago

We return all required data from this, but the business logic requires it be in order of the string declared in our parameter.

Previously when I asked about the business rules, you said, The business rule doesn't matter as the colors need to be hardcoded in the query and return with result set. So I decided that I could use any rule I ilked, and I preferred to randomise.

But I can see there is no correlation between the status and and the Colour in our parameter.

Correct. There is not supposed to be any.

Now you are saying it be in order of the string declared in our parameter. I take this to mean that if the string is Purple, Blue, Brown, Black, the output should be sorted so that Purple comes first. To achieve, you need a split function that returns the list position. I have one here
http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings

Change:

      Create Table #Colours(ID int NOT NULL PRIMARY KEY,
                      Colour Varchar(50) NOT NULL)
            Insert into #colours(id, colour)
            Select ROW_NUMBER() over(Order By (Select newid()))-1, Value
            From dbo.fn_udfSplit(@ColourSQL, ',')

to:

  Create Table #Colours(pos int NOT NULL PRIMARY KEY,
                       Colour Varchar(50) NOT NULL)
  Insert into #colours(id, colour)
    Select listpos, str
    From dbo.iter_charlist_to_tbl(@ColourSQL, ',')

The final query can then be changed to:

;WITH CTE AS (
   Select Number, Status, ROW_NUMBER() OVER(ORDER BY newid()) as rownum
   from #tempCountRichard
)
SELECT CTE.Number, CTE.Status, c.Colour
from   CTE
JOIN   #Colours c on CTE.rownum % (SELECT MAX(pos) FROM #colours) = c.listpos
ORDER BY c.listpos

Now all statuses that happen to be connected to the colour purple will be listed first. There is still no correlation between colours and status, but now you are saying

How can I set it so

Pass = Purple, Pass With Observation = Blue, Fail = Brown, None = Black  and it doesn't change each time I excute.Thanks.

CASE Status
     WHEN 'Pass' THEN 'Purple'
     WHEN 'Pass with observation' THEN 'Blue'
     WHEN 'Fail' THEN 'Brown'
     WHEN 'None' THEN' Black'
END

Or if you want the colours to be determined by the flux of the moment, add a column for status to the Colours table, and put the statuses in the desired position.

Now, what should happen if there are unforseen status values, or few colours than statuses, I don't know.

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI-ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI-ISO Standard SQL. 

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

Temp tables are how non-SQL programmer fake the magnetic scratch tape files they use in the 1950's. Dynamic SQL is how you tell the world you cannot design a vlaid scheam, so you have to let an unknown, random future user take control. 

There is no such crap as a gneric, univesal sartaus in RDBMS. An attribure has to be <something particular>_status; and I have never seen a FIFTY CHARACTER encoding other than the IBAN. Please show me your careful research that came upwith this. The length is the most important part of the design of any data element.   

We do not write UDFs; they cannot be ported or optimized. They cannot be read by the people tryign to fifure out your code. But one again they make your SQL look like 1950's COBOL, FORTRAN or AutoCoder.  

We do not use bit flags in RDBMS; that was assembly language programming which did have scratch tapes. Your deleted: flag is exactly how the old tape files did it! Welcome to 1957! 

Please follow forum rules, and basic Netiquette by showing us the DDL, so we can help you repair this mess. I would  not bother with the insane overhead you have and just use: 

CREATE VIEW Foobar_Counts 
AS
SELECT foobar_status, COUNT(foobar_status) AS foobar_cnt
 FROM Something_Assets
 GROUP BY foobar_status;

Somewhere in the DDL, we should see:

foobar_status VARCHAR (15) NOT NULL
 CHECK (foobar_status IN ('Pass', 'Pass with Obs', 'Fail', 'None')),
 ..

You are doing everything completely wrong and should not be programming SQL. Can you stop and get an education? can you re-do this disaster before the lack of data integrity hurts someone? 

I've totally confused this one... Right, Statuses always remain the same never change never different. Colours never change the amount or the text as hardcoded, Anyway.

returns a table like such

Status	count	colour
pass	xxx	purple
fail	xxx	blue
pwos	xxx	brown
none	xxx	black


xxx is the count for each status

I'm returning the count and the status... Using

Select  
            COUNT(status),
            dbo.GetStatusState(status) Status 
            FROM asset
			WHERE deleted = 0 
			Group By Status

Count	Status
369	Pass                          
26	Fail                          
397	None                          
20	Pass with observations        

I'm using dbo.fu_udfSplit which splits the string up correctly. 

DECLARE @ColourSQL varchar (8000) = 'Purple, Blue, Brown, Black'


            Create Table #Colours( 
                      Colour Varchar(50) NOT NULL)
            Insert into #colours(colour)
            Select Value
            From dbo.fn_udfSplit(@ColourSQL, ',')
            Select Colour from #Colours	 Drop table #Colours

Result.

Purple
Blue
Brown
Black

How do I join it so exact result from table A returns alongside Table B.

So it will always be

Status	count	colour
pass	xxx	purple
fail	xxx	blue
pwos	xxx	brown
none	xxx	black
xxx is the count for each status

So in that string, the convention is that the first colour is for Pass, the second for Fail, the third for Pass for observatio, and the forth is None? And just by chance these the only status values where can be?

In that case, you need a split function that returns the position of the element in the list. Else, all you know is that you got back four colours, and you cannot do more than random matching to your table, because you have no information about order. (Recall that a table is an unordered object.)

Thus, you need to do:

  Create Table #Colours(pos int NOT NULL PRIMARY KEY,
                       Colour Varchar(50) NOT NULL,
  Insert into #colours(pos, Colour)
    Select listpos, str
    From dbo.iter_charlist_to_tbl(@ColourSQL, ',')

Now you have the position, and you can join to the colours table with for instance:

CASE Status
     WHEN 'Pass' THEN 1
     WHEN 'Pass with observation' THEN 2
     WHEN 'Fail' THEN 3
     WHEN 'None' THEN 4
END = #colurs.pos

Create Table #Count (Number Varchar(100), Status Varchar(100), colour varchar(100)) 
            Insert Into #Count (Number, Status)
            Select  
            COUNT(status),
            dbo.GetStatusState(status) Status 
            FROM asset
			WHERE deleted = 0
			Group By Status
			
	        Select 
	        Number, 
	        Status,
	        
	        CASE  Status 
	        when   'Pass'                    Then 'Black'
	        when   'Pass with Observations'  Then 'Brown'
	        when   'Fail'                    Then 'Blue'
	        when   'None'                    Then 'Purple'
	        End As Colour
	        
	        FROM #Count 
	        END

There I go; absolutely yes returning the desired result set! Thank you all.

• Marked as answer by Richard I Evans 12 hours 48 minutes ago