Concatinaded Value - SQL Server Forums

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

Concatinaded Value

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2009-10-14 : 23:26:32

Hi guys,

This is something hard for me and has been stuck me for 1 week.

The SubjectAlert is the main table here. The QueueRegister gets data from ObjectRetrievalGroup (ORG) table. Look at the QueueRegister (QR), some data has 1 digit, 2 digits and 3 digits. Say QR is 9. This belongs to ORG ObjectRetrievalGroupBitValue column. QueueRegister 512 means, the Queue fell in ObjectRetrievalGroupBitValue 5,1 and 2.

My question is how to get the QueueRegister in a separatable digits.

I need a result just like below:



SubjectAlertKey  ObjectRetrievalGroupId
102              QUEUE PRIORITY > 2
170              QUEUE PRIORITY > 2
171              OVERDUEREMINDER
171              VIP
112              LOCAL
112              USERDUEREMINDER
122              VIP
122              USERDUEREMINDER
122              OVERDUEREMINDER
132              VIP
132              USERDUEREMINDER
132              OVERDUEREMINDER


SubjectAlert Table

SubjectAlertKey QueueRegister
--------------- --------------------
102             9
170             9
171             25
112             41
122             512
132             512


ObjectRetrievalGroup Table 

ObjectRetrievalGroupId ObjectRetrievalGroupBitValue
---------------------- ----------------------------
ODAANYBYPRIORITY       0
USERDUEREMINDER        1
OVERDUEREMINDER        2
QIDPRIORITYGTE4        3
LOCAL                  4
VIP                    5
NORMAL VISA            6
NORMAL MASTERCARD      7
FOREIGN                8
QUEUE PRIORITY > 2     9
VISA CARD              10

Please help me.

Thank you very very much.

weipublic
Starting Member

19 Posts

Posted - 2009-10-15 : 21:47:32

[code]WITH CTET (SubjectAlertKey, QueueRegister) AS
(
SELECT SubjectAlertKey,LEFT(QueueRegister,1) FROM SubjectAlert
UNION ALL
SELECT SubjectAlertKey,SUBSTRING(QueueRegister,2,1) FROM SubjectAlert WHERE LEN(QueueRegister)>1
UNION ALL
SELECT SubjectAlertKey,SUBSTRING(QueueRegister,3,1) FROM SubjectAlert WHERE LEN(QueueRegister)>2
)
SELECT c.SubjectAlertKey, o.ODAANYBYPRIORITY
FROM CTET c
INNER JOIN ObjectRetrievalGroup o ON o.ObjectRetrievalGroupBitValue = c.QueueRegister
ORDER BY SubjectAlertKey[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-15 : 23:39:13

[code]
DECLARE @SubjectAlert TABLE
(
SubjectAlertKey int,
QueueRegister varchar(3)
)
INSERT INTO @SubjectAlert
SELECT 102, '9' UNION ALL
SELECT 170, '9' UNION ALL
SELECT 171, '25' UNION ALL
SELECT 112, '41' UNION ALL
SELECT 122, '512' UNION ALL
SELECT 132, '512'

DECLARE @ObjectRetrievalGroup TABLE
(
ObjectRetrievalGroupId varchar(20),
ObjectRetrievalGroupBitValue CHAR(1)
)
INSERT INTO @ObjectRetrievalGroup
SELECT 'ODAANYBYPRIORITY', '0' UNION ALL
SELECT 'USERDUEREMINDER', '1' UNION ALL
SELECT 'OVERDUEREMINDER', '2' UNION ALL
SELECT 'QIDPRIORITYGTE4', '3' UNION ALL
SELECT 'LOCAL', '4' UNION ALL
SELECT 'VIP', '5' UNION ALL
SELECT 'NORMAL VISA', '6' UNION ALL
SELECT 'NORMAL MASTERCARD', '7' UNION ALL
SELECT 'FOREIGN', '8' UNION ALL
SELECT 'QUEUE PRIORITY > 2', '9'

SELECT sak.SubjectAlertKey, org.ObjectRetrievalGroupId
FROM @SubjectAlert sak
CROSS JOIN
(
SELECT n = 1 UNION ALL SELECT n = 2 UNION ALL SELECT n = 3
) n
INNER JOIN @ObjectRetrievalGroup org ON SUBSTRING(sak.QueueRegister, n, 1) = org.ObjectRetrievalGroupBitValue
WHERE SUBSTRING(sak.QueueRegister, n, 1) <> ''
ORDER BY sak.SubjectAlertKey

/*
SubjectAlertKey ObjectRetrievalGroupId
--------------- ----------------------
102 QUEUE PRIORITY > 2
112 LOCAL
112 USERDUEREMINDER
122 VIP
122 USERDUEREMINDER
122 OVERDUEREMINDER
132 VIP
132 USERDUEREMINDER
132 OVERDUEREMINDER
170 QUEUE PRIORITY > 2
171 OVERDUEREMINDER
171 VIP

(12 row(s) affected)
*/
[/code]

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

rpc86
Posting Yak Master

200 Posts

Posted - 2009-10-16 : 00:53:08

That works fine.

Thank you for both replies.

I appreciate you all.

rpc86
Posting Yak Master

200 Posts

Posted - 2009-10-16 : 02:22:04

I tried your suggestions and they work fine in SQL 2005. But when I tried in SQL 2000, there is a syntax error in WITH.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-16 : 02:51:58