sql server 2012 need logic

hi ,
11Ab220111bbb5555ccc4444  i have one value like that i need to print maxmium count value for each value

Eg:
1st value 1 that 1 have 3 time after 0 should print 111
A value is not found anyver so print A
next value b its found maximum bbb

like that  this is one of the interview ques thy asked
kowsika deviAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please find the solution below. Please try and let us know in case of any changes required.

DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444';

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE AS
(
	SELECT *, SUBSTRING(@WeirdValue,Number,1) Nums FROM Series 
	WHERE Number <= LEN(@WeirdValue)
)
,CTE1 AS
(
	SELECT * ,  CASE WHEN Nums = lag(Nums) 
				OVER(ORDER BY Number) THEN 0 ELSE 1 END cols 
	FROM 
	CTE
)
,CTE2 AS
(
	SELECT * , SUM(cols) OVER (PARTITION BY Nums ORDER BY Number) grouper FROM CTE1	
)
,CTE3 AS
(
	SELECT nums FROM CTE2 c WHERE grouper = 2
	UNION ALL
	SELECT DISTINCT nums FROM CTE2 r WHERE grouper = 1
	AND NOT EXISTS ( SELECT NULL FROM CTE2 c1 WHERE r.Nums = c1.Nums AND grouper = 2 )
)
,CTE4 AS
(
	SELECT DISTINCT nums nums1 , STUFF 
					((
					SELECT ', ' + CAST(nums AS VARCHAR)
					FROM CTE3 a
					WHERE ( a.nums = b.nums )
					FOR XML PATH('')
					) ,1,2,'') 
					AS nums
	FROM CTE3 b
)
SELECT nums1 Number , nums PrintAs FROM CTE4

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
Number PrintAs
------ ------------------------
0      0
1      1, 1, 1
2      2
4      4
5      5
A      A
b      b, b, b
c      c

(8 row(s) affected)

Open in new window

0
 
Nitin SontakkeDeveloperCommented:
Are you also saying that 11 should never get reported?
0
 
ste5anSenior DeveloperCommented:
Where is your database related question?

Cause the one you've given is violating 1NF..

You can start with

DECLARE @WeirdValue NVARCHAR(255) = N'11Ab220111bbb5555ccc4444';

WITH Nums
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY A.object_id ) AS N
     FROM   sys.objects A ,
            sys.objects B )
SELECT   SC.SingleCharacter ,
         COUNT(*) AS Occurences
FROM     Nums
         CROSS APPLY ( SELECT SUBSTRING(@WeirdValue, Nums.N, 1) AS SingleCharacter ) SC
WHERE    Nums.N <= LEN(@WeirdValue)
GROUP BY SC.SingleCharacter;

Open in new window

1
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PortletPaulfreelancerCommented:
>>"this is one of the interview ques thy asked"

If I asked this question, I would expect the person to tell me not to store the data like that and that the table needs re-design.
I am serious, that is the answer I would expect.
1
 
kowsika deviAuthor Commented:
i need maximum value eg 1 has APPEAR 3 TIMES AFTER O SO IT SHOULD GIVE 111
NEXT A  it hs no ver so give A alone like that.
0
 
PortletPaulfreelancerCommented:
LEARN TSQL FUNCTIONS

If you want to succeed at interviews you will need to actually know the subject. Asking us will not substitute for reading and trying it yourself.

e.g.
CHARINDEX (Transact-SQL)

(This will tell you where the zero is)

Look carefully at what Ste5an gave you... did you try it?
0
 
Pawan KumarDatabase ExpertCommented:
Question abandoned. Provided solution needed by the author.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.