June 5, 2014 at 7:35 am
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be
column header US
with value Project/Prescription sale
next column header NL
with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[tooltip] [nvarchar](254) NULL,
[saleTypeCat_id] [int] NOT NULL,
)
GO
INSERT INTO [#SALETYPE]
(
SaleType_Id
,name
,tooltip
,saleTypeCat_id
)
SELECT 5, 'US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning";FI:"Projekti/suunnittelijamyynti";FR:"Projet / Vente par prescription";SP:"Proyecto /Prescripción";IT:"Vendita progetto/prescrizione"' ,'US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning";FI:"Projekti/suunnittelijamyynti";FR:"Projet / Vente par prescription";SP:"Proyecto /Prescripción";IT:"Vendita progetto/prescrizione"' ,1 UNION ALL
SELECT 6, 'US:"Direct sale roofer";NL:"Directe verkoop dakdekkers";SW:"Direktförsäljning till TE";FI:"Suoramyynti kattourakoitsijoille";FR:"Ventes directes";SP:"Venta directa al instalador";IT:"Vendita diretta impermeabilizzatori"' ,'US:"Direct sale roofer";NL:"Directe verkoop dakdekkers";SW:"Direktförsäljning till TE";FI:"Suoramyynti kattourakoitsijoille"' ,0 UNION ALL
SELECT 7, 'US:"Service & Maintenance sale";FI:"Huoltomyynti";FR:"Ventes de services";SP:"Venta de servicio";IT:"Vendita di servizi"' ,'US:"Service & Maintenance sale";FI:"Huoltomyynti";FR:"Ventes de services";SP:"Venta de servicio";IT:"Vendita di servizi"' ,0 UNION ALL
SELECT 8, 'US:"Sales to distributor";FR:"Ventes distribution";SP:"Venta distribución";IT:"Vendite distribuzione"' ,'U' ,0
June 5, 2014 at 7:46 am
Do you have a fixed amount of languages?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 7:51 am
There is a limit but there will be a different number of languages in different lists. So I will need to search for a value ex US: and return the value in quotes after us: but if SW: doesn't exist the it return '' (nothing)
June 5, 2014 at 7:52 am
June 5, 2014 at 7:58 am
hmmm. maybe we have >20 lists but maybe I can use the same temp #table as in my post. I still need help with the string manipulation to extract the values after the country code: and between the quotes, I havn't figured that one out.
June 5, 2014 at 8:10 am
Well, the language-specific description always begins at the 6th character, and ends at the (n-1)th character, where n is the total length of the string. You can use SUBSTRING to extract it. Have a go, and post back if there's anything in particular that you struggle with.
John
June 5, 2014 at 8:21 am
Sorry, I don't understand. The name and Tooltip column can be up to 239 characters long, actually up to 4048 characters using extended tables.
Can you explain how you got to 6 chraracters? for exampel: 'US:"Project/Prescription sale"; The actuel label is after 4 characters and -1 after the ;
Then how do I get the next language? As you can see, strings aren't my strong point.
June 5, 2014 at 8:27 am
You need to use the splitter I linked to earlier in order to separate the languages out. Then you will be left with strings like this:
[font="Courier New"]US:"Project/Prescription sale"[/font]
The bits you're interested in are [font="Courier New"]US[/font], which is always the first two characters of the string, and [font="Courier New"]Project/Prescription sale[/font], which always starts at the fifth character (sorry, I thought there was a space as well, which is why I originally said sixth), and ends at the last character but one.
John
June 5, 2014 at 9:01 am
If you insert your information into a properly normalized table, the rest is easier.
To do so, you can use the DelimitedSplit8K found in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
And work with something like this:
WITH Names AS(
SELECT SaleType_Id,
LEFT( Item, 2) AS Country,
SUBSTRING( Item, 5, LEN(Item) - 6) AS name
FROM #SALETYPE
CROSS APPLY dbo.DelimitedSplit8K( name, ';') s
WHERE LEN(Item) > 6
),
Tooltips AS(
SELECT SaleType_Id,
LEFT( Item, 2) AS Country,
SUBSTRING( Item, 5, LEN(Item) - 6) AS tooltip
FROM #SALETYPE
CROSS APPLY dbo.DelimitedSplit8K( tooltip, ';') s2
WHERE LEN(Item) > 6
)
SELECT ISNULL( n.SaleType_Id, t.SaleType_Id) AS SaleType_Id,
ISNULL( n.Country, t.Country) AS Country,
ISNULL( name,'') AS name,
ISNULL( tooltip,'') AS tooltip
FROM Names n
FULL
JOIN Tooltips t ON n.SaleType_Id = t.SaleType_Id AND n.Country = t.Country
With that you could use cross tabs to pivot the table. Read about them in these articles:
October 2, 2014 at 6:39 am
I've almost got it now, but I have one more problem. I need to get the results for each saletype on one row for each saletype_id. I now get 5 rows for each saletype. I tried using distinct but that didn't work
WITH Names AS
(
SELECT saletype_Id,
LEFT( Item, 2) AS Country,
SUBSTRING( Item, 5, LEN(Item) - 5) AS name
FROM #saletype
CROSS APPLY dbo.DelimitedSplit8K( name, ';') s
WHERE LEN(Item) > 5
)
SELECT 'saletype' AS List
, n.saletype_id
--ISNULL( n.saletype_Id,'') AS saletype_id,
--ISNULL( n.Country,'') AS Country,
--ISNULL( name,'') AS name
, ToolTip_US_or_agreed= CASE WHEN n.country='US' THEN name ELSE '' END
, US= CASE WHEN n.country='US' THEN name ELSE '' END
, SW= CASE WHEN n.country='SW' THEN name ELSE '' END
, SP= CASE WHEN n.country='SP' THEN name ELSE '' END
, NL= CASE WHEN n.country='NL' THEN name ELSE '' END
, PL= CASE WHEN n.country='PL' THEN name ELSE '' END
, IT= CASE WHEN n.country='IT' THEN name ELSE '' END
, FR= CASE WHEN n.country='FR' THEN name ELSE '' END
, FI= CASE WHEN n.country='FI' THEN name ELSE '' END
, NO= CASE WHEN n.country='NO' THEN name ELSE '' END
, DA= CASE WHEN n.country='DA' THEN name ELSE '' END
, GE= CASE WHEN n.country='GE' THEN name ELSE '' END
, CZ= CASE WHEN n.country='CZ' THEN name ELSE '' END
, CZ= CASE WHEN n.country='CZ' THEN name ELSE '' END
, RU= CASE WHEN n.country='RU' THEN name ELSE '' END
, JP= CASE WHEN n.country='JP' THEN name ELSE '' END
FROM Names n
ORDER BY n.saletype_id
October 2, 2014 at 8:17 am
You need to add an aggregate function to your CASE statements. It's all explained in the articles that I mentioned earlier this year.
October 2, 2014 at 8:17 am
I tried making a cursor, but it gave the same results, so I obviously did something wrong here.
What's new in this question is that I have included all the possible languages.
Below is the cursor I tried to make:
/*
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,
[tooltip] [nvarchar](254) NULL,
[saleTypeCat_id] [int] NOT NULL,
)
GO
INSERT INTO [#SALETYPE]
(
SaleType_Id
,name
,tooltip
,saleTypeCat_id
)
SELECT 5, 'US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning";FI:"Projekti/suunnittelijamyynti";FR:"Projet / Vente par prescription";SP:"Proyecto /Prescripción";IT:"Vendita progetto/prescrizione"' ,'US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning";FI:"Projekti/suunnittelijamyynti";FR:"Projet / Vente par prescription";SP:"Proyecto /Prescripción";IT:"Vendita progetto/prescrizione"' ,1 UNION ALL
SELECT 6, 'US:"Direct sale roofer";NL:"Directe verkoop dakdekkers";SW:"Direktförsäljning till TE";FI:"Suoramyynti kattourakoitsijoille";FR:"Ventes directes";SP:"Venta directa al instalador";IT:"Vendita diretta impermeabilizzatori"' ,'US:"Direct sale roofer";NL:"Directe verkoop dakdekkers";SW:"Direktförsäljning till TE";FI:"Suoramyynti kattourakoitsijoille"' ,0 UNION ALL
SELECT 7, 'US:"Service & Maintenance sale";FI:"Huoltomyynti";FR:"Ventes de services";SP:"Venta de servicio";IT:"Vendita di servizi"' ,'US:"Service & Maintenance sale";FI:"Huoltomyynti";FR:"Ventes de services";SP:"Venta de servicio";IT:"Vendita di servizi"' ,0 UNION ALL
SELECT 8, 'US:"Sales to distributor";FR:"Ventes distribution";SP:"Venta distribución";IT:"Vendite distribuzione"' ,'U' ,0
*/
/*
CREATE TABLE [#SaleBiz](
[SaleType_id] [int] NOT NULL,
[US] [nvarchar](200) NULL,
[SW] [nvarchar](200) NULL,
[SP] [nvarchar](200) NULL,
[NL] [nvarchar](200) NULL,
[PL] [nvarchar](200) NULL,
[IT] [nvarchar](200) NULL,
[FR] [nvarchar](200) NULL,
[FI] [nvarchar](200) NULL,
[NO] [nvarchar](200) NULL,
[DA] [nvarchar](200) NULL,
[GE] [nvarchar](200) NULL,
[CZ] [nvarchar](200) NULL,
[CN] [nvarchar](200) NULL,
[RU] [nvarchar](200) NULL,
[JP] [nvarchar](200) NULL
) ON [PRIMARY]
GO
*/
SET NOCOUNT ON
DECLARE @SaleType_id int
DECLARE @Country NVARCHAR(200)
DECLARE @name NVARCHAR (200)
DECLARE @US NVARCHAR(200)
DECLARE @SW NVARCHAR(200)
DECLARE @sp-2 NVARCHAR(200)
DECLARE @NL NVARCHAR(200)
DECLARE @PL NVARCHAR(200)
DECLARE @IT NVARCHAR(200)
DECLARE @FR NVARCHAR(200)
DECLARE @FI NVARCHAR(200)
DECLARE @NO NVARCHAR(200)
DECLARE @DA NVARCHAR(200)
DECLARE @GE NVARCHAR(200)
DECLARE @CZ NVARCHAR(200)
DECLARE @CN NVARCHAR(200)
DECLARE @RU NVARCHAR(200)
DECLARE @jp-2 NVARCHAR(200)
DECLARE SaleType_cursor CURSOR FOR
WITH Names AS
(
SELECT saletype_Id,
LEFT( Item, 2) AS Country,
SUBSTRING( Item, 5, LEN(Item) - 5) AS name
FROM #saletype
CROSS APPLY dbo.DelimitedSplit8K( name, ';') s
WHERE LEN(Item) > 5
)
SELECT --'saletype' AS List
n.saletype_id
, US= CASE WHEN n.country='US' THEN name ELSE '' END
, SW= CASE WHEN n.country='SW' THEN name ELSE '' END
, SP= CASE WHEN n.country='SP' THEN name ELSE '' END
, NL= CASE WHEN n.country='NL' THEN name ELSE '' END
, PL= CASE WHEN n.country='PL' THEN name ELSE '' END
, IT= CASE WHEN n.country='IT' THEN name ELSE '' END
, FR= CASE WHEN n.country='FR' THEN name ELSE '' END
, FI= CASE WHEN n.country='FI' THEN name ELSE '' END
, NO= CASE WHEN n.country='NO' THEN name ELSE '' END
, DA= CASE WHEN n.country='DA' THEN name ELSE '' END
, GE= CASE WHEN n.country='GE' THEN name ELSE '' END
, CZ= CASE WHEN n.country='CZ' THEN name ELSE '' END
, CZ= CASE WHEN n.country='CZ' THEN name ELSE '' END
, RU= CASE WHEN n.country='RU' THEN name ELSE '' END
, JP= CASE WHEN n.country='JP' THEN name ELSE '' END
FROM Names n
ORDER BY n.saletype_id
OPEN SaleType_cursor
FETCH NEXT FROM SaleType_cursor
INTO
@SaleType_id
,@US
,@SW
,@SP
,@NL
,@PL
,@IT
,@FR
,@FI
,@NO
,@DA
,@GE
,@CZ
,@CN
,@RU
,@JP
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO [#SaleBiz]
(
[SaleType_id],
[US],
[SW],
[SP],
[NL],
[PL],
[IT],
[FR],
[FI],
[NO],
[DA],
[GE],
[CZ],
[CN],
[RU],
[JP]
)
VALUES
(
@SaleType_id
,@US
,@SW
,@SP
,@NL
,@PL
,@IT
,@FR
,@FI
,@NO
,@DA
,@GE
,@CZ
,@CN
,@RU
,@JP
)
FETCH NEXT FROM SaleType_cursor
INTO
@SaleType_id
,@US
,@SW
,@SP
,@NL
,@PL
,@IT
,@FR
,@FI
,@NO
,@DA
,@GE
,@CZ
,@CN
,@RU
,@JP
END
CLOSE SaleType_cursor
DEALLOCATE SaleType_cursor
SET NOCOUNT OFF
select * from #SaleBiz
October 2, 2014 at 8:30 am
That's where I'm unsuccessful. I've tried group by and made a cursor, but I always get the same results.
October 2, 2014 at 9:10 am
You used group by, but did you use the aggregate function? Are you sure you know what you're grouping by? No cursor is needed. In fact, the solution is so simple that you might hit your head.
WITH Names AS
(
SELECT saletype_Id,
LEFT( Item, 2) AS Country,
SUBSTRING( Item, 5, LEN(Item) - 5) AS name
FROM #saletype
CROSS APPLY dbo.DelimitedSplit8K( name, ';') s
WHERE LEN(Item) > 5
)
SELECT 'saletype' AS List
, n.saletype_id
--ISNULL( n.saletype_Id,'') AS saletype_id,
--ISNULL( n.Country,'') AS Country,
--ISNULL( name,'') AS name
, ToolTip_US_or_agreed= MAX( CASE WHEN n.country='US' THEN name ELSE '' END )
, US= MAX( CASE WHEN n.country='US' THEN name ELSE '' END)
, SW= MAX( CASE WHEN n.country='SW' THEN name ELSE '' END)
, SP= MAX( CASE WHEN n.country='SP' THEN name ELSE '' END)
, NL= MAX( CASE WHEN n.country='NL' THEN name ELSE '' END)
, PL= MAX( CASE WHEN n.country='PL' THEN name ELSE '' END)
, IT= MAX( CASE WHEN n.country='IT' THEN name ELSE '' END)
, FR= MAX( CASE WHEN n.country='FR' THEN name ELSE '' END)
, FI= MAX( CASE WHEN n.country='FI' THEN name ELSE '' END)
, NO= MAX( CASE WHEN n.country='NO' THEN name ELSE '' END)
, DA= MAX( CASE WHEN n.country='DA' THEN name ELSE '' END)
, GE= MAX( CASE WHEN n.country='GE' THEN name ELSE '' END)
, CZ= MAX( CASE WHEN n.country='CZ' THEN name ELSE '' END)
, CZ= MAX( CASE WHEN n.country='CZ' THEN name ELSE '' END)
, RU= MAX( CASE WHEN n.country='RU' THEN name ELSE '' END)
, JP= MAX( CASE WHEN n.country='JP' THEN name ELSE '' END)
FROM Names n
GROUP BY n.saletype_id
ORDER BY n.saletype_id
October 2, 2014 at 9:26 am
Wow, Why make it hard when it can be easy. I was close, but so far away.
Thank you so much!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply