» »

tsql kolona iz vrednosti druge kolone

tsql kolona iz vrednosti druge kolone

korenje3 ::

Ali je možno narediti, da vrednost iz ene kolone uporabi v drugi koloni?



Recimo iz kolone "Property Name" bi rad imel 2 dodatni koloni "Naziv" in "Ident" kamor bi dalo rezultate, ki so trenutno v "Property Value".

Trenutni sql ukaz je pa tak:
WITH [FilteredSource] AS (
-- partition: 0 ms
SELECT
	[Documents].[DocumentID] AS [DocumentIDP],
	[Documents].[Filename] AS [Datoteka],
	[Documents].[Link] AS [Link],
	[Documents].[Deleted] AS [Deleted],
	[Documents].[LatestRevisionNo] AS [Revizija],
	[Projects].[ProjectID] AS [ProjectID],
	[Projects].[Deleted] AS [DeletedProj],
	[Projects].[Path] AS [DirektorijAggr],
	[Users].[FullName] AS [CheckOut By],
	[DocType].[TypeName] AS [TypeName],
	[VariableValue].[ValueText] AS [Property Value],
	[ObjectType].[ObjectTypeID] AS [ObjectTypeID],
	[ObjectType].[Description] AS [Description],
	[Variable].[IsDeleted] AS [IsDeleted],
	[Variable].[VariableName] AS [Property Name],
	[FileExtension].[Extension] AS [Extension],
	[DocumentConfiguration].[ConfigurationID] AS [ConfigurationID],
	[DocumentConfiguration].[ConfigurationName] AS [Konfiguracija],
	[Revisions].[FlushedDate] AS [Checked-In],
	ROW_NUMBER() OVER (PARTITION BY [DocumentConfiguration].[ConfigurationID], [Variable].[VariableID], [Documents].[DocumentID] ORDER BY [Revisions].[FlushedDate] DESC) AS [Partition_Konf-Prop]
-- select: 0 ms
FROM 	[DocumentsInProjects]
	FULL JOIN [Documents] ON [DocumentsInProjects].[DocumentID] = [Documents].[DocumentID]
	FULL JOIN [Projects] ON [DocumentsInProjects].[ProjectID] = [Projects].[ProjectID]
	FULL JOIN [VariableValue] ON [Documents].[DocumentID] = [VariableValue].[DocumentID]
	FULL JOIN [Variable] ON [VariableValue].[VariableID] = [Variable].[VariableID]
	FULL JOIN [Revisions] ON [Documents].[DocumentID] = [Revisions].[DocumentID]
	FULL JOIN [Users] ON [Documents].[UserID] = [Users].[UserID]
	FULL JOIN [DocType] ON [Documents].[DocTypeID] = [DocType].[DocTypeID]
	FULL JOIN [FileExtension] ON [Documents].[ExtensionID] = [FileExtension].[ExtensionID]
	FULL JOIN [ObjectType] ON [Documents].[ObjectTypeID] = [ObjectType].[ObjectTypeID]
	FULL JOIN [DocumentConfiguration] ON [VariableValue].[ConfigurationID] = [DocumentConfiguration].[ConfigurationID]
-- join: 0 ms
WHERE	([Documents].[Filename] LIKE CAST('%ngi%' AS NVARCHAR(5)))
AND	([Documents].[Link] = CAST('0' AS BIT))
AND	([Documents].[Deleted] = CAST('0' AS BIT))
AND	([Projects].[Deleted] = CAST('0' AS BIT))
AND	([DocType].[TypeName] NOT LIKE CAST('Toolbox' AS NVARCHAR(128)))
AND	([ObjectType].[Description] LIKE CAST('Normal Document' AS NVARCHAR(255)))
AND	([Variable].[IsDeleted] = CAST('0' AS BIT)
OR	 [Variable].[IsDeleted] IS NULL)
AND	([Variable].[VariableName] LIKE CAST('%ident%' AS NVARCHAR(7))
OR	 [Variable].[VariableName] LIKE CAST('%naziv%' AS NVARCHAR(7)))
-- filter: 0 ms
)
-- recursion: 0 ms
SELECT TOP 2000
	CONCAT('C:\EPDM', [FilteredSource].[DirektorijAggr]) AS [Direktorij],
	[FilteredSource].[Datoteka] AS [Datoteka],
	CONCAT('conisio://EPDM/open?projectid=', [FilteredSource].[ProjectID], '&documentid=', [FilteredSource].[DocumentIDP], '&objecttype=', [FilteredSource].[ObjectTypeID]) AS [Open],
	[FilteredSource].[Extension] AS [Extension],
	[FilteredSource].[Revizija] AS [Revizija],
	[FilteredSource].[Konfiguracija] AS [Konfiguracija],
	[FilteredSource].[Property Name] AS [Property Name],
	[FilteredSource].[Property Value] AS [Property Value],
	[FilteredSource].[CheckOut By] AS [CheckOut By],
	[FilteredSource].[Checked-In] AS [Checked-In],
	[FilteredSource].[DocumentIDP] AS [DocumentIDP],
	[FilteredSource].[ConfigurationID] AS [ConfigurationID]
-- selectouter: 0 ms
FROM 	[FilteredSource]
-- join: 0 ms
WHERE 	[FilteredSource].[Partition_Konf-Prop] <= 1

GROUP BY
	[FilteredSource].[DocumentIDP],
	[FilteredSource].[Datoteka],
	[FilteredSource].[Link],
	[FilteredSource].[Deleted],
	[FilteredSource].[Revizija],
	[FilteredSource].[ProjectID],
	[FilteredSource].[DeletedProj],
	[FilteredSource].[DirektorijAggr],
	[FilteredSource].[CheckOut By],
	[FilteredSource].[TypeName],
	[FilteredSource].[Property Value],
	[FilteredSource].[ObjectTypeID],
	[FilteredSource].[Description],
	[FilteredSource].[IsDeleted],
	[FilteredSource].[Property Name],
	[FilteredSource].[Extension],
	[FilteredSource].[ConfigurationID],
	[FilteredSource].[Konfiguracija],
	[FilteredSource].[Checked-In]
-- groupby: 16 ms
HAVING	 (CONCAT('C:\EPDM', [FilteredSource].[DirektorijAggr]) NOT LIKE CAST('%staro%' AS TEXT))
-- having: 16 ms
ORDER BY
	[FilteredSource].[DirektorijAggr],
	[FilteredSource].[DocumentIDP],
	[FilteredSource].[Konfiguracija] ASC,
	CASE
	WHEN [FilteredSource].[Property Name] LIKE 'Ident' THEN 1
	WHEN [FilteredSource].[Property Name] LIKE 'St. risbe' THEN 2
	WHEN [FilteredSource].[Property Name] LIKE 'Author' THEN 3
	WHEN [FilteredSource].[Property Name] LIKE 'Konstruiral' THEN 4
	WHEN [FilteredSource].[Property Name] LIKE 'Drawn By' THEN 5
	ELSE 6 END ASC,
	[FilteredSource].[Property Name]
-- orderby: 16 ms
;
i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
  • spremenil: korenje3 ()

smetko ::

Poglej si funkcijo iif()
Primer:
iif([Variable].[VariableName]='ident',[VariableValue].[ValueText],null) as ident
No comment

korenje3 ::

Ja sam se mi zdi da bi to izpisovalo samo 1 vrednost v 1 vrsti. Mene pa zanima kako obe vrednosti izpisat v 1 vrsti.
i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Lonsarg ::

Najprej moras seveda vedit po kateri lastnosti bi zdruzeval vrstice, ugibam da po "konfiguracija".

Nato pa nic komplicirat amlak cisto preprosto en self join naredis z pogojem kjer ima "leva" tabela vrednost Naziv, "desna" pa Ident.

Zgodovina sprememb…

  • spremenil: Lonsarg ()

korenje3 ::

to pomeni da moram v eni tabeli iskat samo po ident, po drugi pa samo naziv?
Torej bi lahko dupliciral
WITH [FilteredSource] AS ()
kjer bi v eni imel filter
([Variable].[VariableName] LIKE CAST('%ident%' AS NVARCHAR(7))
v drugi pa
[Variable].[VariableName] LIKE CAST('%naziv%' AS NVARCHAR(7))


To pomeni tudi 2x daljšo izvedbo iskanja?

hm dejansko bi bila taka izvedba dokaj preprosta saj se mi ukazna vrstica generira po segmentih...
i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Zgodovina sprememb…

  • spremenil: korenje3 ()

Lonsarg ::

Ja to bi bil brute force način.

Performančni način pa je da greš čimbolj na izvor tam kjer se duplikacija zgodi. Če sem prav nahitro pogledal tvoj query se to pri tebi zgodi pri tabeli VariableValue, če ga nisem polomil bi že tu lahko razbil join da enkrat vrne eno lastnost drugi drugo na ta način:
INNER JOIN [VariableValue] AS identValue ON [Documents].[DocumentID] = identValue.[DocumentID]
INNER JOIN [Variable] AS ident ON identValue.[VariableID] = ident.[VariableID] AND ident.VariableName = 'Ident'
INNER JOIN [VariableValue] AS nazivValue ON [Documents].[DocumentID] = nazivValue.[DocumentID] AND
INNER JOIN [Variable] AS naziv ON nazivValue.[VariableID] = naziv.[VariableID] AND naziv.VariableName = 'Naziv'

Uporabil sem aliase kot se spodobi, v selectu lahko dobiš preko teh dveh aliasov potem obe lastnosti. Ker sem uporabil inner join ti ne bo vrnilo vrstic kjer te dve lastnosti manjkata, tisto bi potem moral še malenkost drugače če bi tudi take rabil.

Zgodovina sprememb…

  • spremenil: Lonsarg ()


Vredno ogleda ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

TSQL rekurzivno

Oddelek: Programiranje
11918 (657) hamez66
»

[SQL] Filtriranje

Oddelek: Programiranje
7914 (669) korenje3
»

SQL vprašanje

Oddelek: Programiranje
81120 (719) MrStein
»

[Java]Problem z vektorjem

Oddelek: Programiranje
91466 (1335) KernelPanic
»

Apache - localhost JA, IP naslov NE

Oddelek: Omrežja in internet
186622 (6481) ManDriver

Več podobnih tem