Please help to sort this

Question

Please help to sort this

ss.meghana8

SSC Rookie

Points: 26
More actions
February 10, 2014 at 3:26 am

#98874

I have a table like
Column1
-------
1-greend=asjnlkj
3-red=jljlkj
2=green<79
2-yellow>90
2-red<yu
I need it to be like
-------
Column1 Column2
Green 2
Red 2
Yellow 1

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLCJ SSCommitted Points: 1791 More actions · Answer 1

Does this mean that you want count() of color in the second column and color name in the first? Also please provide create and insert statement for the sample data.

ss.meghana8 SSC Rookie Points: 26 More actions · Answer 2

Yes i want colours in first column and count in second column.

CREATE TABLE COLORS

(

COLORS NVARCHAR(100)

)

INSERT INTO COLORS VALUES '1 - Green = FHG'

INSERT INTO COLORS VALUES ' 2 - 3< Amber <10'

INSERT INTO COLORS VALUES '3 - Red >=10'

INSERT INTO COLORS VALUES '2 - 50%< Amber <90%'

INSERT INTO COLORS VALUES '3 - Red <HJGFJ'

INSERT INTO COLORS VALUES '1 - Green>=JGHJ%'

SQLCJ SSCommitted Points: 1791 More actions · Answer 3

Initially, I thought of fetching the color names depending using substring() but thought it might take longer to write proper SUBSTRING() hence thought doing following way.. it might be easy way out 🙂 but at least it's a way out :-D...

DECLARE @REFERENCE TABLE -- this table will hold the color names that you want count against

(

ID INT IDENTITY(1,1),

COLOR VARCHAR(100)

)

CREATE TABLE #OUTPUT -- this is to store the output of the result set

(

COLOR VARCHAR(100),

CNT INT

)

INSERT INTO @REFERENCE

SELECT 'RED' UNION ALL

SELECT 'AMBER' UNION ALL

SELECT 'GREEN'

DECLARE @QUERY VARCHAR(1000),

@LOOP INT,

@COL VARCHAR(100)

SELECT @LOOP = MAX(ID) FROM @REFERENCE

WHILE (@LOOP >= 1 )

BEGIN

SELECT @COL = COLOR FROM @REFERENCE WHERE ID = @LOOP

SET @QUERY = 'SELECT ''' + @COL + ''',COUNT(1) FROM COLORS WHERE DATA LIKE ''%' + @COL + '%'''

INSERT INTO #OUTPUT EXEC(@QUERY)

SET @LOOP = @LOOP - 1

END

SELECT * FROM #OUTPUT

DROP TABLE #OUTPUT

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

This is the easy way out!

DROP TABLE #COLORS

CREATE TABLE #COLORS (COLORS VARCHAR(50))

INSERT INTO #COLORS

SELECT '1 - Green = FHG' UNION ALL

SELECT ' 2 - 3< Amber <10' UNION ALL

SELECT '3 - Red >=10' UNION ALL

SELECT '2 - 50%< Amber <90%' UNION ALL

SELECT '3 - Red <HJGFJ' UNION ALL

SELECT '1 - Green>=JGHJ%'

SELECT

ref.colour,

ColourCount = COUNT(*)

FROM #COLORS c

CROSS APPLY (VALUES ('Green'),('Amber'),('Red'),('blue')) ref (colour)

WHERE c.COLORS LIKE '%'+ref.colour+'%'

GROUP BY ref.colour

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ss.meghana8 SSC Rookie Points: 26 More actions · Answer 5

Thanku...It worked for me bit how can i convert

colourColourCount

Green2

Amber2

Red2

to

Green Amber Red

2 2 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Do you have a fixed number of colours? If so, can you list them please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ss.meghana8 SSC Rookie Points: 26 More actions · Answer 7

ss.meghana8

SSC Rookie

Points: 26

February 10, 2014 at 7:03 am

#1687522

I have 2 columns colour,countof

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

ss.meghana8 (2/10/2014)
I have 2 columns colour,countof

How many colours?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLCJ SSCommitted Points: 1791 More actions · Answer 9

@ ChrisM@Work - What you did is actually a easy way out. thanks for bursting my bubble ..This has prompted me to learn how APPLY works.. This is one of those concept that I'm not able to understand since long... anyway I'll try again ...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

chetan.deshpande001 (2/10/2014)
@ ChrisM@Work - What you did is actually a easy way out. thanks for bursting my bubble ..This has prompted me to learn how APPLY works.. This is one of those concept that I'm not able to understand since long... anyway I'll try again ...

The two links in my sig block will help you there.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden