How to write a condition in SQL using the COUNT value
Hi iam has two tables and I need to insert values into temp table according to counter like
IF(@COUNT>1)
BEGIN
insert into #temp_cols
SELECT M.ID_PK,
substring(M.NAME,1,1)+'_'+ N.NAME
FROM TEST_TABLE1 M WITH (NOLOCK)
LEFT JOIN TEST_TABLE2 N ON M.ID_PK=N.ID_FK
END
ELSE
BEGIN
insert into #temp_cols
SELECT M.ID_PK,
N.NAME
FROM TEST_TABLE1 M WITH (NOLOCK)
LEFT JOIN TEST_TABLE2 N ON M.ID_PK=N.ID_FK
END
where @count should be equal to selecting count (name) from the test_table2 group by name that returns a group of columns, and @count should take one column value at a time
Here's another way. If the idea displays the name differently depending on the presence of duplicates in TestTable2, then this will work:
DECLARE @Count int
SELECT @Count = COUNT(name) FROM test_table2 GROUP BY name HAVING COUNT(name) > 1
INSERT INTO #temp_cols
SELECT
M.ID_PK,
CASE
WHEN @Count > 1 THEN SUBSTRING(M.NAME, 1, 1) + '_' + N.NAME
ELSE N.Name
END,
FROM TEST_TABLE1 M WITH (NOLOCK)
LEFT JOIN TEST_TABLE2 N ON M.ID_PK = N.ID_FK
EDIT: If, on the other hand, the intention is to change the display of the name only when there are matching ids in TestTable2, this will work and it is very simple:
INSERT INTO #temp_cols
SELECT
M.ID_PK,
ISNULL(SUBSTRING(M.NAME, 1, 1) + '_' + N.NAME, M.Name) As Name
FROM TEST_TABLE1 M WITH (NOLOCK)
LEFT JOIN TEST_TABLE2 N ON M.ID_PK = N.ID_FK
a source to share
I created some test data like this:
id_pk | name | id_fk | name
-------+------+-------+--------
1 | foo | 1 | jingle
1 | foo | 1 | jangle
2 | bar | 2 | jangle
3 | quux | |
And I wrote a request:
select m.id_pk, case when groupcount.name_count > 1 then substring(m.name, 1, 1) + '_' + n.name else m.name end
from test_table1 m
left join test_table2 n on m.id_pk = n.id_fk
left join (select name, count(name) as name_count from test_table2 group by name) groupcount on n.name = groupcount.name
This creates the following:
id_pk | name
-------+----------
1 | foo
1 | f_jangle
2 | b_jangle
3 | quux
I think you could mean m.name and not n.name in your else branch?
I'm guessing (and so a sample output would be helpful) from your "@count should take one column value at a time" that @count needs to infer from the number of rows for each name in test_table2.
I believe the output of this query is what you want to go into your temp table: in this case, the prefix "insert into #temp_cols".
a source to share
IF EXISTS (select count(name) from test_table2 group by name HAVING count(name) > 1)
BEGIN
insert into #temp_cols
SELECT M.ID_PK,
substring(M.NAME,1,1)+'_'+ N.NAME
FROM TEST_TABLE1 M WITH (NOLOCK)
LEFT JOIN TEST_TABLE2 N ON M.ID_PK=N.ID_FK
END
ELSE
BEGIN
insert into #temp_cols
SELECT M.ID_PK,
N.NAME
FROM TEST_TABLE1 M WITH (NOLOCK)
LEFT JOIN TEST_TABLE2 N ON M.ID_PK=N.ID_FK
END
a source to share