J'essaye de concaténer un tas de colonnes en SQL.
Le problème est qu'il n'y a pas de nombre défini de colonnes et qu'il pourrait donc être nécessaire de concaténer 18 colonnes en une seule fois et 30 la suivante.
Les structures des tableaux ressemblent à:
concatbase :
SELECT DISTINCT
DENSE_RANK() OVER (PARTITION BY propertyid ORDER BY plannumber) AS rank,
propertyid,
CASE WHEN ConcatField_1 IS NULL THEN ' ' + planprefix + plannumber ELSE ConcatField_1 END +
CASE WHEN ConcatField_2 IS NULL AND ConcatField_1 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_2 is null and ConcatField_1 IS NULL then '' ELSE ' & ' + ConcatField_2 END +
CASE WHEN ConcatField_3 IS NULL AND ConcatField_2 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_3 is null and ConcatField_2 IS NULL then '' ELSE ' & ' + ConcatField_3 END +
CASE WHEN ConcatField_4 IS NULL AND ConcatField_3 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_4 is null and ConcatField_3 IS NULL then '' ELSE ' & ' + ConcatField_4 END +
CASE WHEN ConcatField_5 IS NULL AND ConcatField_4 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_5 is null and ConcatField_4 IS NULL then '' ELSE ' & ' + ConcatField_5 END +
CASE WHEN ConcatField_6 IS NULL AND ConcatField_5 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_6 is null and ConcatField_5 IS NULL then '' ELSE ' & ' + ConcatField_6 END +
CASE WHEN ConcatField_7 IS NULL AND ConcatField_6 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_7 is null and ConcatField_6 IS NULL then '' ELSE ' & ' + ConcatField_7 END +
CASE WHEN ConcatField_8 IS NULL AND ConcatField_7 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_8 is null and ConcatField_7 IS NULL then '' ELSE ' & ' + ConcatField_8 END +
CASE WHEN ConcatField_9 IS NULL AND ConcatField_8 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_9 is null and ConcatField_8 IS NULL then '' ELSE ' & ' + ConcatField_9 END +
CASE WHEN ConcatField_10 IS NULL AND ConcatField_9 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_10 is null and ConcatField_9 IS NULL then '' ELSE ' & ' + ConcatField_10 END +
CASE WHEN ConcatField_11 IS NULL AND ConcatField_10 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_11 is null and ConcatField_10 IS NULL then '' ELSE ' & ' + ConcatField_11 END +
CASE WHEN ConcatField_12 IS NULL AND ConcatField_11 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_12 is null and ConcatField_11 IS NULL then '' ELSE ' & ' + ConcatField_12 END +
CASE WHEN ConcatField_13 IS NULL AND ConcatField_12 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_13 is null and ConcatField_12 IS NULL then '' ELSE ' & ' + ConcatField_13 END +
CASE WHEN ConcatField_14 IS NULL AND ConcatField_13 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_14 is null and ConcatField_13 IS NULL then '' ELSE ' & ' + ConcatField_14 END +
CASE WHEN ConcatField_15 IS NULL AND ConcatField_14 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_15 is null and ConcatField_14 IS NULL then '' ELSE ' & ' + ConcatField_15 END +
CASE WHEN ConcatField_16 IS NULL AND ConcatField_15 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_16 is null and ConcatField_15 IS NULL then '' ELSE ' & ' + ConcatField_16 END +
CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_17 is null and ConcatField_16 IS NULL then '' ELSE ' & ' + ConcatField_17 END +
CASE WHEN ConcatField_18 IS NULL AND ConcatField_17 IS NOT NULL THEN ' ' + planprefix + plannumber when ConcatField_18 is null and ConcatField_17 IS NULL then '' ELSE ' & ' + ConcatField_18 END AS semi_rpd
INTO
#RPD_STAGING
FROM
#concat_base
Table RPD_STAGING (et résultat attendu à partir de l'exemple de données ID 1)
Rank | ID | semi_rp | --------+-----------+---------------------------+ 1 | 1 | 100-150 & 300 & 302 P11 | 2 | 1 | 101 P11 | 3 | 1 | 608-908 & 1010 P2222 |
J'utilise cette instruction SQL pour concaténer ceci ensemble:
ID | planprefix | plannumber | ConcatField_1 | ConcatField_2 | ConcatField_3 | .... ----+------------+------------+---------------+---------------+---------------+ .... 1 | p | 11 | 100-150 | 300 | 302 | .... 1 | P | 111 | 101 | NULL | NULL | .... 1 | P | 2222 | 600-908 | 1010 | NULL | .... 4 | D | 33333 | 400-406 | NULL | NULL | .... 5 | D | 444444 | 300 | NULL | NULL | .... 6 | p | 19 | 200 | 300-308 | 400 | ....
Quelqu'un peut-il m'aider à créer une solution qui pourrait traiter un nombre quelconque de ConcatFields?
Si possible, je voudrais m'en tenir à SQL, mais si nécessaire, je suis ouvert à l'utilisation d'autres outils. Si vous avez besoin de plus d'informations, veuillez me le faire savoir.
3 Réponses :
Je pense qu'il vaut mieux utiliser CONCAT () fonction SQL intégrée que Case, car elle peut gérer une valeur nulle. par exemple, vous pouvez utiliser
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank,
propertyid,
CONCAT(
planprefix, plannumber, ConcatField_1,
ConcatField_2...
) INTO #RPD_STAGING
FROM
#concat_base
Merci pour le conseil. Je ne savais pas que Concat () autoriserait les valeurs nulles. Connaissez-vous un moyen de traiter aucun nombre défini de champs? Je suppose que je pourrais écrire 100 champs concat, mais si possible, je voudrais un moyen de sorte que dans le cas où 101 seraient nécessaires, il échouera ou manquera des données.
CONCAT () gère les solutions ISNULL () et COALESCE () dans ce cas. La concaténation de chaînes et de valeurs numériques avec l'expression «+» peut entraîner des erreurs de syntaxe. La solution peut donc être d'utiliser l'une de ces fonctions.
@MatthewHancock Vous auriez besoin de connaître les colonnes dans la phase d'analyse lorsque vous écrivez la requête SQL. Donc vous voudriez concat (col1, col2, ..., coln) présent dans la table concat_base
Ce code devrait permettre de concaténer sur un nombre inconnu de colonnes commençant par "ConcatField_" Je ne parviens pas à corriger / déboguer le code (je n'utilise pas de serveur SQL), mais tous les éléments sont là pour permettre la concaténation dynamique sur un nombre inconnu de champs en supposant qu'ils commencent tous par ConcatField.
SELECT
DISTINCT DENSE_RANK() OVER(
PARTITION BY propertyid
ORDER BY
plannumber
) AS rank, P,
propertyid,
) INTO #RPD_STAGING
FROM
#concat_base,
CROSS APPLY (SELECT CONCAT(', ' , col.Name)
FROM INFORMATION_SCHEMA.COLUMNS AS col
WHERE col like 'ConcatField*'
FOR XML PATH('') ) AS P (Concat_list)
Merci pour toutes les suggestions, j'ai fini par réécrire le tout.
Au lieu de générer un nombre indéfini de colonnes, j'ai fait quelque chose comme ce qui suit!
DROP TABLE IF EXISTS #temp,#temp2
CREATE TABLE #temp
(
ID INT
,Parcel int
,lot NVARCHAR(255)
);
INSERT INTO #temp(ID, Parcel, lot)
VALUES
(1,111 ,1 ),(1,111 ,2 ),(1,111 ,3 ),(2,1212,1 ),(2,1212,3 ),(2,1212,4 ),(3,1333,1 ),(3,1333,7 ),(4,5555,1 ),(4,5555,7 )
,(4,5544,1 ),(4,5544,2 ),(5,1809,1 ),(5,1809,2 ),(5,1809,3 ),(5,1809,5 ),(5,1810,6 ),(5,1810,7 ),(5,1810,8 )
SELECT ID,Parcel,
(SELECT lot + ','
FROM #temp p2
WHERE p1.ID = p2.ID
AND p1.Parcel = p2.Parcel
ORDER BY ID,Parcel
FOR XML PATH ('')) AS Products
INTO #temp2 FROM #temp p1
GROUP BY p1.ID,p1.Parcel
SELECT * FROM #temp2
Vous pouvez peut-être créer une requête SQL dynamique. En concaténant les commandes SQL sous forme de morceaux de chaîne pour une instruction finale, puis exécutez-la avec la commande sp_executesql comme indiqué dans l'exemple kodyaz.com/articles/...
Je suis familier avec les instructions SQL dynamiques, mais je ne sais pas exactement comment les utiliser dans ce cas. Pourriez-vous donner un exemple rapide de la façon dont je pourrais l'utiliser pour en écrire un pour ce problème?
Vous pouvez interroger la vue système SYS.TABLE_COLUMNS et rechercher le nombre maximal de noms de champs tels que «ConcatField_%». Lorsque vous avez ces informations, vous pouvez ajouter la plupart des fragments de code sql suivants "CASE WHEN ConcatField_17 IS NULL AND ConcatField_16 IS NOT NULL THEN '' + planprefix + plannumber lorsque ConcatField_17 est nul et ConcatField_16 IS NULL puis '' ELSE '&' + ConcatField_17 END + "
@MatthewHancock. . . Une table a un nombre fixe de colonnes. Je ne comprends pas votre modèle de données, mais vous rencontrez un problème si le nombre de colonnes dans une table peut changer.