July 13, 2016 at 6:00 am
I have 2 tables where i can search on PK. However 1 table has other columns than the other. Is there a way I can use the same query (except for changing tablename) with having the columns converted to rows?
CREATE TABLE [dbo].[Test](
[PK] [int] IDENTITY(1,1) NOT NULL,
[COLUMN1] [nvarchar](50) NOT NULL,
[COLUMN2] [nchar](10) NULL,
[COLUMN3] [nchar](10) NULL,
[COLUMN4] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r1'
,'Valuein2r1'
,'VAluein3r1'
,'Valuein4r1')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r2'
,'Valuein2r2'
,'VAluein3r2'
,'Valuein4r2')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r3'
,'Valuein2r3'
,'VAluein3r3'
,'Valuein4r3')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r4'
,'Valuein2r4'
,'VAluein3r4'
,'Valuein4r4')
GO
select *
from test
where PK = 1
This will get me:
1Value in 1Value in 2VAlue in 3Value in 4
However, what i would like as result is:
select 1, 'Value in 1'
union
select 1, 'Value in 2'
union
select 1, 'Value in 3'
union
select 1, 'Value in 4'
Is there a way to achieve this, and when having a 'test2' i only need to change the tablename in the select?
July 13, 2016 at 6:11 am
Quick suggestion
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
CREATE TABLE [dbo].[Test](
[PK] [int] IDENTITY(1,1) NOT NULL,
[COLUMN1] [nvarchar](50) NOT NULL,
[COLUMN2] [nchar](10) NULL,
[COLUMN3] [nchar](10) NULL,
[COLUMN4] [nchar](10) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r1'
,'Valuein2r1'
,'VAluein3r1'
,'Valuein4r1')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r2'
,'Valuein2r2'
,'VAluein3r2'
,'Valuein4r2')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r3'
,'Valuein2r3'
,'VAluein3r3'
,'Valuein4r3')
GO
INSERT INTO [dbo].[Test]
([COLUMN1]
,[COLUMN2]
,[COLUMN3]
,[COLUMN4])
VALUES
('Valuein1r4'
,'Valuein2r4'
,'VAluein3r4'
,'Valuein4r4')
GO
SELECT
TT.PK
,X.COL
FROMdbo.TestTT
CROSS APPLY
(
SELECT TT.COLUMN1 UNION ALL
SELECT TT.COLUMN2 UNION ALL
SELECT TT.COLUMN3 UNION ALL
SELECT TT.COLUMN4
) AS X(COL);
Output
PK COL
--- -----------
1 Valuein1r1
1 Valuein2r1
1 VAluein3r1
1 Valuein4r1
2 Valuein1r2
2 Valuein2r2
2 VAluein3r2
2 Valuein4r2
3 Valuein1r3
3 Valuein2r3
3 VAluein3r3
3 Valuein4r3
4 Valuein1r4
4 Valuein2r4
4 VAluein3r4
4 Valuein4r4
July 13, 2016 at 6:19 am
Good for this table, however i cannot change the tablename without changing the 'cross apply' part, so for my other table, i need to change the whole query, in stead of only the table name.
July 13, 2016 at 6:36 am
peter 67432 (7/13/2016)
Good for this table, however i cannot change the tablename without changing the 'cross apply' part, so for my other table, i need to change the whole query, in stead of only the table name.
Why not? The table name is not referenced in the CROSS APPLY.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2016 at 6:37 am
Eirikur, is your test database really called 'TEEST'? 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2016 at 6:38 am
Sorry, you are right. I ment changing the column names :unsure:
July 13, 2016 at 6:47 am
peter 67432 (7/13/2016)
Sorry, you are right. I ment changing the column names :unsure:
So you want something which handles different column names dynamically?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2016 at 6:51 am
That's correct.
July 13, 2016 at 7:08 am
Like this?
DECLARE @SQL nvarchar(MAX) = N'',
@Table nvarchar(128) = N'Test'
SELECT @SQL = N'SELECT
TT.PK
,X.COL
FROMdbo.' + QUOTENAME(@Table) + N'TT
CROSS APPLY
(
' + STUFF(( SELECT N' UNION ALL
SELECT TT.' + QUOTENAME(name)
FROM sys.columns
WHERE object_id = OBJECT_ID(@Table, 'U')
AND is_identity = 0
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 13, '')
+ N'
) AS X(COL);'
EXEC sp_executesql @SQL;
July 13, 2016 at 8:37 am
Phil Parkin (7/13/2016)
Eirikur, is your test database really called 'TEEST'? 🙂
Yees
😎
July 13, 2016 at 8:46 am
Exacty! thanks!
July 13, 2016 at 8:57 am
Nice, Luis.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 13, 2016 at 9:14 am
peter 67432 (7/13/2016)
Exacty! thanks!
Do you understand how the code works? Please, post questions you might have or you might not be able to support it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply