-1

I have two tables as shown in the example. Now I want to select the data in the format presented in the comment lines below.

create table cust (nbr varchar(8))
create table data (nbr varchar(8),fld varchar(8),val varchar(8))

insert into cust (nbr) values ('AA')
insert into data (nbr,fld,val) values ('AA','1','one')
insert into data (nbr,fld,val) values ('AA','2','two')
insert into data (nbr,fld,val) values ('AA','3','three')
insert into data (nbr,fld,val) values ('AA','1','uno')
insert into data (nbr,fld,val) values ('AA','2','dos')
insert into data (nbr,fld,val) values ('AA','3','tres')

select * from cust
select * from data

drop table cust
drop table data

-- AA, One, Two, Three
-- AA, Uno, Dos, Tres

Any ideas how to join these tables to get the desired output.

Masoud Abrishami
  • 81
  • 9
Rene
  • 1
  • 1
  • 1
    What related the values `'One'` and `'Two'` why not `'One'` and `'Dos'`? (Remember, SQL server doesn't understand common language, so all three of those are simply 3 character `varchar` values.) – Thom A Oct 11 '19 at 12:15
  • What if there is a fourth row for a customer? – Salman A Oct 11 '19 at 12:23
  • I don't see any easy way to do it. You'd have to manually specify the allowed matches for val. BUT I think you could add another "language" table that has LanguageID and AllowedWords. Then you could use that table to join with and then get the output you want with a pivot. – avery_larry Oct 11 '19 at 12:29
  • Learning some database design will be a good start. – Eric Oct 11 '19 at 22:20

1 Answers1

0

At the moment you don't have anything in the data table which would allow you to group the rows into anything you can work with. If you add a languageID column to the data table as suggested by @avery_larry, you could write your select as follows:

create table cust (nbr varchar(8))
create table data (nbr varchar(8),fld varchar(8),val varchar(8), languageID int)

insert into cust (nbr) values ('AA')
insert into data (nbr,fld,val, languageID) values ('AA','1','one', 1)
insert into data (nbr,fld,val, languageID) values ('AA','2','two', 1)
insert into data (nbr,fld,val, languageID) values ('AA','3','three', 1)
insert into data (nbr,fld,val, languageID) values ('AA','1','uno', 2)
insert into data (nbr,fld,val, languageID) values ('AA','2','dos', 2)
insert into data (nbr,fld,val, languageID) values ('AA','3','tres', 2);

select * from cust;
select * from data;


with data_cte(nbr, languageID)
as
(
    select nbr, languageID
    from data with (nolock)
    group by nbr, languageID
)
select c.nbr,
    SUBSTRING(
        (
            SELECT ','+d.val AS [text()]
            FROM [data] d
            WHERE d.nbr = dc.nbr and d.languageID = dc.languageID
            ORDER BY d.fld
            FOR XML PATH ('')
        ), 2, 1000) as 'Values'
from data_cte dc
inner join cust c on c.nbr = dc.nbr;

drop table cust
drop table data

The sql for concatenating the strings was taken from this post How to concatenate text from multiple rows into a single text string in SQL server?, with a slight tweek to add the with.

Hope this helps.

lardymonkey
  • 728
  • 1
  • 16
  • 34