transpose query varchar bigint conversion

Question

I have the following sql query (this is a transpose query to get comma separated table)

    SELECT CAST ((SELECT taxonomy_id + ','
                  FROM content
                  FOR XML PATH('')) AS bigint) AS NewTaxonomytableName

But I am getting Error converting data type varchar to bigint. The table taxonomy_id is a bigint

Any ideas?

UPDATE:

SELECT CAST ((SELECT CAST(taxonomy_id AS varchar) + ',' AS Expr1 FROM taxonomy_item_tbl FOR XML PATH('')) AS varchar) AS Expr1

Running this gives me one row.

But in fact it is not right for me. It needs to comma separate only the taxonomy_id's for a specific content_id.

An example:

content_id taxonomy_id
1          15 
1          16 
5          24 
5          19

needs to be:

content_id taxonomy_id
1          15,16
5          24,19

I am really stuck with this for 8 hours. If anyone can help me I will really appreciate it...

Thanks

You're casting the Comma Separated List to BIGINT. But `'1,2,3,4'` can't be cast to a BIGINT... CAST to NVARCHAR(max) or something instead? Or just don't CAST it at all? — MatBailie, Dec 12 '11 at 14:18

John N · Accepted Answer · 2011-12-12T16:30:10.177

1

The problem is that you aren't converting taxonomy_id to a bigint, you're converting a comma-seperated list of them to a bigint.

Try this instead:

SELECT CAST ((SELECT CAST(taxonomy_id as varchar) + ','
              FROM content
              FOR XML PATH('')) AS varchar(max)) AS NewTaxonomytableName

EDIT Ok, since I'm not exactly certain of your table structure, you might need to play around with this to get exactly what you want, but the theory is right.

SELECT DISTINCT c.content_id,
    CAST((SELECT CAST(taxonomy_id as varchar) + ','
          FROM content
          WHERE content_id = c.content_id
          FOR XML PATH('')) AS varchar(max)) AS taxonomy_ids
FROM content c

edited Dec 12 '11 at 16:30

answered Dec 12 '11 at 14:17

John N

1,755
17
21

Try now - I think you would have been getting a slightly different error: `Error converting data type bigint to varchar.` as opposed to `Error converting data type varchar to bigint.` – John N Dec 12 '11 at 14:28
Break it down and check your data. My starting diagnostic would be does `SELECT CAST(taxonomy_id as varchar) + ',' FROM content` return any rows? – John N Dec 12 '11 at 14:38
Ok, then how about `SELECT CAST(taxonomy_id as varchar) + ',' FROM content FOR XML PATH('')`? – John N Dec 12 '11 at 15:12
I ran following `SELECT CAST ((SELECT CAST(taxonomy_id AS varchar) + ',' AS Expr1 FROM taxonomy_item_tbl FOR XML PATH('')) AS varchar) AS Expr1` But I only get one row now? – Ozkan Dec 12 '11 at 15:26
@Ozkan - Yes that is to be expected. One row with all `taxonomy_id` concatenated. – Mikael Eriksson Dec 12 '11 at 15:44
@Mikael Eriksson Hi, but I want to wrap all taxonomy_id of each content_id to one content_id (one row per content_id) – Ozkan Dec 12 '11 at 15:46
@Ozkan - How should we know that. You have not mentioned any of this in your question. Pleas update your question with table structures some sample data and expected output. – Mikael Eriksson Dec 12 '11 at 15:51
@Mikael Eriksson Hi, I updated the topic. Please help me with this. I have an headache of thinking for a solution... :) – Ozkan Dec 12 '11 at 16:00

Mikael Eriksson · Answer 2 · 2011-12-12T16:44:29.073

declare @T table
(
  content_id int,
  taxonomy_id int
)

insert into @T values
(1,          15), 
(1,          16), 
(5,          24), 
(5,          19)


select T1.content_id,
       stuff((select ','+cast(T2.taxonomy_id as varchar(20))
              from @T as T2       
              where T1.content_id = T2.content_id
              for xml path(''),  type).value('.', 'varchar(max)'), 1, 1, '') as taxeconomy_id
from @T as T1
group by T1.content_id

Result:

content_id  taxeconomy_id
----------- -------------
1           15,16
5           24,19

transpose query varchar bigint conversion

2 Answers2