none
Need Help with Cocatenate

    Question

  • Hi All,

    I'm having there columns that i want to concatenate. PrincipalId,InterestId and FeeId. They have values as 0 and 1. So i'm looking for something on the lines 001,010,100,111,110,101...

    I'm using the following way to concatenate without any success.

    select k.principaid+' '+k.interestid+' '+k.feeid

    from K

    The output i was getting was k.principalidk.interestidk.feeid

    Please any advice is greatly appreciated.


    Novice

    Saturday, February 16, 2013 12:03 AM

Answers

  • declare @sno table (a bit,b bit,c bit)
    
    insert into @sno
    values (1,1,1)
    
    select convert(varchar(1),a)+convert(varchar(1),b)+convert(varchar(1),c)
    from @sno


    Hope it Helps!!


    • Edited by Stan210 Saturday, February 16, 2013 12:24 AM
    • Marked as answer by Novicessrs Saturday, February 16, 2013 12:27 AM
    Saturday, February 16, 2013 12:18 AM

All replies

  • declare @sno table (a bit,b bit,c bit)
    
    insert into @sno
    values (1,1,1)
    
    select convert(varchar(1),a)+convert(varchar(1),b)+convert(varchar(1),c)
    from @sno


    Hope it Helps!!


    • Edited by Stan210 Saturday, February 16, 2013 12:24 AM
    • Marked as answer by Novicessrs Saturday, February 16, 2013 12:27 AM
    Saturday, February 16, 2013 12:18 AM
  • Thanks a lot stan

    Novice

    Saturday, February 16, 2013 12:26 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    You need more help than you can get here. Unfortunately, you will get kludges that will let you get into more and more trouble.  We do not use BIT data in SQL; that was assembly language. An identifier is a subset of attributes of an entity; do you really have only two of each of those things?? And what would the concatenation mean in a valid logical data model? (if I put my hat size, shoe size and blood type in a string, what is the logical name of that data element?).

    Back up and start with a book on data modeling.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, February 18, 2013 3:08 AM