February 14, 2019 at 2:42 am
I have these two tables CREATE TABLE MyTable
(
WillField VARCHAR(10),
Surname VARCHAR(30),
FirstName VARCHAR(30),
CellPhone INT
);
INSERT INTO MyTable
VALUES
('00508093X1', 'Smith', 'John', 1234),
('00508093X2', 'Brown', 'Will', 1234),
('00508093X3', 'Cool', 'Joe', 1234),
('00508093X4', 'PeeKay', 'Tim', 1234),
('00508093X5', 'Shoes', 'Thabo', 1234),
('00508093X6', 'Blue', 'Tshepo', 1234),
('00508093X7', 'North', 'Lebo', 1234),
('00508093X8', 'Come', 'Kagiso', 1234),
('00508093X9', 'Home', 'Oreng', 1234),
('0050809X10', 'SeeYou', 'Daarso', 1234)
CREATE TABLE MyTable2
(
AttributeName VARCHAR(10),
AttributeValue VARCHAR(30)
);
I wan the data in the first table to be PIVOTed into the second table, with AttributeName having Column Names and AttributeValue the value of the columns. AttributeName | AttributeValue
-------------------------------
WillField | '00508093X1'
Surname | 'Smith'
FirstName | 'John'
CellPhone | 1231
February 14, 2019 at 4:32 am
INSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)
SELECT * FROM MyTable2
February 14, 2019 at 5:10 am
Jonathan AC Roberts - Thursday, February 14, 2019 4:32 AMINSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)SELECT * FROM MyTable2
Is it then possible to query myTable 2 and have those four fields (WillField,Surname,FirstName,CellPhone) as Columns with their respective values as Rows? and how do we cater for duplicates, that we shouldn't load the same data on the next run?
February 14, 2019 at 5:28 am
mediacommentry - Thursday, February 14, 2019 5:10 AMJonathan AC Roberts - Thursday, February 14, 2019 4:32 AMINSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)SELECT * FROM MyTable2
Is it then possible to query myTable 2 and have those four fields (WillField,Surname,FirstName,CellPhone) as Columns with their respective values as Rows? and how do we cater for duplicates, that we shouldn't load the same data on the next run?
I'm not sure what you mean. I've got no idea what you want to use this table for. Do you mean that a surname should not appear twice in the table? For example, the name Smith is not allowed to appear more than once? What if there were more than one person with the name Smith? If that's what you mean though you can just add a not exists to the query to stop any duplicate rows:INSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)
WHERE NOT EXISTS(SELECT *
FROM MyTable2 t
WHERE t.AttributeName = x.AttributeName
AND t.AttributeValue = x.AttributeValue)
Also, there is no way to group the columns back together in the four fields (WillField,Surname,FirstName,CellPhone) as there is nothing to identify which rows should be grouped together.
February 14, 2019 at 5:42 am
Jonathan AC Roberts - Thursday, February 14, 2019 5:28 AMmediacommentry - Thursday, February 14, 2019 5:10 AMJonathan AC Roberts - Thursday, February 14, 2019 4:32 AMINSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)SELECT * FROM MyTable2
Is it then possible to query myTable 2 and have those four fields (WillField,Surname,FirstName,CellPhone) as Columns with their respective values as Rows? and how do we cater for duplicates, that we shouldn't load the same data on the next run?
I'm not sure what you mean. I've got no idea what you want to use this table for. Do you mean that a surname should not appear twice in the table? For example, the name Smith is not allowed to appear more than once? What if there were more than one person with the name Smith? If that's what you mean though you can just add a not exists to the query to stop any duplicate rows:
INSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)
WHERE NOT EXISTS(SELECT *
FROM MyTable2 t
WHERE t.AttributeName = x.AttributeName
AND t.AttributeValue = x.AttributeValue)
I want to load it to MyTable2, then we able to query it again, but when I query it from MyTable2 I want it the format of the MyTable, (which is WillField Surname FirstName CellPhone)
00508093X1 Smith John 1231
00508093X2 Brown Will 1232
February 14, 2019 at 5:45 am
mediacommentry - Thursday, February 14, 2019 5:42 AMJonathan AC Roberts - Thursday, February 14, 2019 5:28 AMmediacommentry - Thursday, February 14, 2019 5:10 AMJonathan AC Roberts - Thursday, February 14, 2019 4:32 AMINSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)SELECT * FROM MyTable2
Is it then possible to query myTable 2 and have those four fields (WillField,Surname,FirstName,CellPhone) as Columns with their respective values as Rows? and how do we cater for duplicates, that we shouldn't load the same data on the next run?
I'm not sure what you mean. I've got no idea what you want to use this table for. Do you mean that a surname should not appear twice in the table? For example, the name Smith is not allowed to appear more than once? What if there were more than one person with the name Smith? If that's what you mean though you can just add a not exists to the query to stop any duplicate rows:
INSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)
WHERE NOT EXISTS(SELECT *
FROM MyTable2 t
WHERE t.AttributeName = x.AttributeName
AND t.AttributeValue = x.AttributeValue)I want to load it to MyTable2, then we able to query it again, but when I query it from MyTable2 I want it the format of the MyTable, (which is
WillField Surname FirstName CellPhone)
00508093X1 Smith John 1231
00508093X2 Brown Will 1232
There is no way to group the columns back together in the four fields (WillField,Surname,FirstName,CellPhone) from MyTable2 as there is nothing to identify which rows should be grouped together. You would need to change the definition of MyTable2 to have something to say which rows they originally belonged to.
February 14, 2019 at 5:51 am
Jonathan AC Roberts - Thursday, February 14, 2019 5:45 AMmediacommentry - Thursday, February 14, 2019 5:42 AMJonathan AC Roberts - Thursday, February 14, 2019 5:28 AMmediacommentry - Thursday, February 14, 2019 5:10 AMJonathan AC Roberts - Thursday, February 14, 2019 4:32 AMINSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)SELECT * FROM MyTable2
Is it then possible to query myTable 2 and have those four fields (WillField,Surname,FirstName,CellPhone) as Columns with their respective values as Rows? and how do we cater for duplicates, that we shouldn't load the same data on the next run?
I'm not sure what you mean. I've got no idea what you want to use this table for. Do you mean that a surname should not appear twice in the table? For example, the name Smith is not allowed to appear more than once? What if there were more than one person with the name Smith? If that's what you mean though you can just add a not exists to the query to stop any duplicate rows:
INSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)
WHERE NOT EXISTS(SELECT *
FROM MyTable2 t
WHERE t.AttributeName = x.AttributeName
AND t.AttributeValue = x.AttributeValue)I want to load it to MyTable2, then we able to query it again, but when I query it from MyTable2 I want it the format of the MyTable, (which is
WillField Surname FirstName CellPhone)
00508093X1 Smith John 1231
00508093X2 Brown Will 1232There is no way to group the columns back together in the four fields (WillField,Surname,FirstName,CellPhone) as there is nothing to identify which rows should be grouped together. You would need to change the definition of MyTable2 to have something to say which rows they originally belonged to.
I will another column, BatchID, which will have a number for every load. CREATE TABLE #MyTable2 if the first load was 50 rows, all of them will have the same value, e.g
(
BatchId INT,
AttributeName VARCHAR(10),
AttributeValue VARCHAR(30)
);AttributeName AttributeValue
100, WillField 00508093X1
100, Surname Smith
100, FirstName John
100, CellPhone 1231
100, WillField 00508093X2
100, Surname Brown
100, FirstName Will
100, CellPhone 1232
February 14, 2019 at 6:07 am
mediacommentry - Thursday, February 14, 2019 5:51 AMJonathan AC Roberts - Thursday, February 14, 2019 5:45 AMmediacommentry - Thursday, February 14, 2019 5:42 AMJonathan AC Roberts - Thursday, February 14, 2019 5:28 AMmediacommentry - Thursday, February 14, 2019 5:10 AMJonathan AC Roberts - Thursday, February 14, 2019 4:32 AMINSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)SELECT * FROM MyTable2
Is it then possible to query myTable 2 and have those four fields (WillField,Surname,FirstName,CellPhone) as Columns with their respective values as Rows? and how do we cater for duplicates, that we shouldn't load the same data on the next run?
I'm not sure what you mean. I've got no idea what you want to use this table for. Do you mean that a surname should not appear twice in the table? For example, the name Smith is not allowed to appear more than once? What if there were more than one person with the name Smith? If that's what you mean though you can just add a not exists to the query to stop any duplicate rows:
INSERT INTO MyTable2
SELECT x.AttributeName, x.AttributeValue
FROM myTable
CROSS APPLY(values ('WillField',WillField),
('Surname',Surname),
('FirstName',FirstName),
('CellPhone',CONVERT(varchar(9),CellPhone))) x(AttributeName,AttributeValue)
WHERE NOT EXISTS(SELECT *
FROM MyTable2 t
WHERE t.AttributeName = x.AttributeName
AND t.AttributeValue = x.AttributeValue)I want to load it to MyTable2, then we able to query it again, but when I query it from MyTable2 I want it the format of the MyTable, (which is
WillField Surname FirstName CellPhone)
00508093X1 Smith John 1231
00508093X2 Brown Will 1232There is no way to group the columns back together in the four fields (WillField,Surname,FirstName,CellPhone) as there is nothing to identify which rows should be grouped together. You would need to change the definition of MyTable2 to have something to say which rows they originally belonged to.
I will another column, BatchID, which will have a number for every load.
CREATE TABLE #MyTable2if the first load was 50 rows, all of them will have the same value, e.g
(
BatchId INT,
AttributeName VARCHAR(10),
AttributeValue VARCHAR(30)
);AttributeName AttributeValue
100, WillField 00508093X1
100, Surname Smith
100, FirstName John
100, CellPhone 1231
100, WillField 00508093X2
100, Surname Brown
100, FirstName Will
100, CellPhone 1232
You need an Id for each row in MyTable not for each batch load.
February 14, 2019 at 7:12 am
How about we batch these and properly identify it all, as follows:CREATE TABLE #MyTable (
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
BatchID INT NOT NULL,
WillField VARCHAR(10),
Surname VARCHAR(30),
FirstName VARCHAR(30),
CellPhone INT
);
INSERT INTO #MyTable (BatchID, WillField, Surname, FirstName, CellPhone)
VALUES (100, '00508093X1', 'Smith', 'John', 1230),
(100, '00508093X2', 'Brown', 'Will', 1231),
(100, '00508093X3', 'Cool', 'Joe', 1232),
(100, '00508093X4', 'PeeKay', 'Tim', 1233),
(100, '00508093X5', 'Shoes', 'Thabo', 1234),
(100, '00508093X6', 'Blue', 'Tshepo', 1235),
(100, '00508093X7', 'North', 'Lebo', 1236),
(100, '00508093X8', 'Come', 'Kagiso', 1237),
(100, '00508093X9', 'Home', 'Oreng', 1238),
(100, '0050809X10', 'SeeYou', 'Daarso', 1239);
CREATE TABLE #MyTable2 (
BatchID INT NOT NULL,
RowID INT NOT NULL,
AttributeName VARCHAR(10),
AttributeValue VARCHAR(30)
);
INSERT INTO #MyTable2 (BatchID, RowID, AttributeName, AttributeValue)
SELECT
MT.BatchID,
MT.RowID,
CA.AttributeName,
CA.AttributeValue
FROM #MyTable AS MT
CROSS APPLY (
SELECT 'WillField' AS AttributeName, MT.WillField AS AttributeValue
UNION ALL
SELECT 'Surname', MT.Surname
UNION ALL
SELECT 'FirstName', MT.FirstName
UNION ALL
SELECT 'CellPhone', CONVERT(char(4), MT.CellPhone)
) AS CA
SELECT *
FROM #MyTable2;
SELECT
MT.BatchID,
MT.RowID,
MAX(CASE MT.AttributeName WHEN 'WillField' THEN MT.AttributeValue END) AS WillField,
MAX(CASE MT.AttributeName WHEN 'Surname' THEN MT.AttributeValue END) AS Surname,
MAX(CASE MT.AttributeName WHEN 'FirstName' THEN MT.AttributeValue END) AS FirstName,
MAX(CASE MT.AttributeName WHEN 'CellPhone' THEN MT.AttributeValue END) AS CellPhone
FROM #MyTable2 AS MT
GROUP BY
MT.BatchID,
MT.RowID
ORDER BY
MT.BatchID,
MT.RowID;
DROP TABLE #MyTable;
DROP TABLE #MyTable2;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 14, 2019 at 9:24 pm
sgmunson - Thursday, February 14, 2019 7:12 AMHow about we batch these and properly identify it all, as follows:CREATE TABLE #MyTable (
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
BatchID INT NOT NULL,
WillField VARCHAR(10),
Surname VARCHAR(30),
FirstName VARCHAR(30),
CellPhone INT
);INSERT INTO #MyTable (BatchID, WillField, Surname, FirstName, CellPhone)
VALUES (100, '00508093X1', 'Smith', 'John', 1230),
(100, '00508093X2', 'Brown', 'Will', 1231),
(100, '00508093X3', 'Cool', 'Joe', 1232),
(100, '00508093X4', 'PeeKay', 'Tim', 1233),
(100, '00508093X5', 'Shoes', 'Thabo', 1234),
(100, '00508093X6', 'Blue', 'Tshepo', 1235),
(100, '00508093X7', 'North', 'Lebo', 1236),
(100, '00508093X8', 'Come', 'Kagiso', 1237),
(100, '00508093X9', 'Home', 'Oreng', 1238),
(100, '0050809X10', 'SeeYou', 'Daarso', 1239);CREATE TABLE #MyTable2 (
BatchID INT NOT NULL,
RowID INT NOT NULL,
AttributeName VARCHAR(10),
AttributeValue VARCHAR(30)
);INSERT INTO #MyTable2 (BatchID, RowID, AttributeName, AttributeValue)
SELECT
MT.BatchID,
MT.RowID,
CA.AttributeName,
CA.AttributeValue
FROM #MyTable AS MT
CROSS APPLY (
SELECT 'WillField' AS AttributeName, MT.WillField AS AttributeValue
UNION ALL
SELECT 'Surname', MT.Surname
UNION ALL
SELECT 'FirstName', MT.FirstName
UNION ALL
SELECT 'CellPhone', CONVERT(char(4), MT.CellPhone)
) AS CASELECT *
FROM #MyTable2;SELECT
MT.BatchID,
MT.RowID,
MAX(CASE MT.AttributeName WHEN 'WillField' THEN MT.AttributeValue END) AS WillField,
MAX(CASE MT.AttributeName WHEN 'Surname' THEN MT.AttributeValue END) AS Surname,
MAX(CASE MT.AttributeName WHEN 'FirstName' THEN MT.AttributeValue END) AS FirstName,
MAX(CASE MT.AttributeName WHEN 'CellPhone' THEN MT.AttributeValue END) AS CellPhone
FROM #MyTable2 AS MT
GROUP BY
MT.BatchID,
MT.RowID
ORDER BY
MT.BatchID,
MT.RowID;DROP TABLE #MyTable;
DROP TABLE #MyTable2;
Thank you Steve 🙂🙂
February 15, 2019 at 7:49 am
mediacommentry - Thursday, February 14, 2019 9:24 PMsgmunson - Thursday, February 14, 2019 7:12 AMHow about we batch these and properly identify it all, as follows:CREATE TABLE #MyTable (
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
BatchID INT NOT NULL,
WillField VARCHAR(10),
Surname VARCHAR(30),
FirstName VARCHAR(30),
CellPhone INT
);INSERT INTO #MyTable (BatchID, WillField, Surname, FirstName, CellPhone)
VALUES (100, '00508093X1', 'Smith', 'John', 1230),
(100, '00508093X2', 'Brown', 'Will', 1231),
(100, '00508093X3', 'Cool', 'Joe', 1232),
(100, '00508093X4', 'PeeKay', 'Tim', 1233),
(100, '00508093X5', 'Shoes', 'Thabo', 1234),
(100, '00508093X6', 'Blue', 'Tshepo', 1235),
(100, '00508093X7', 'North', 'Lebo', 1236),
(100, '00508093X8', 'Come', 'Kagiso', 1237),
(100, '00508093X9', 'Home', 'Oreng', 1238),
(100, '0050809X10', 'SeeYou', 'Daarso', 1239);CREATE TABLE #MyTable2 (
BatchID INT NOT NULL,
RowID INT NOT NULL,
AttributeName VARCHAR(10),
AttributeValue VARCHAR(30)
);INSERT INTO #MyTable2 (BatchID, RowID, AttributeName, AttributeValue)
SELECT
MT.BatchID,
MT.RowID,
CA.AttributeName,
CA.AttributeValue
FROM #MyTable AS MT
CROSS APPLY (
SELECT 'WillField' AS AttributeName, MT.WillField AS AttributeValue
UNION ALL
SELECT 'Surname', MT.Surname
UNION ALL
SELECT 'FirstName', MT.FirstName
UNION ALL
SELECT 'CellPhone', CONVERT(char(4), MT.CellPhone)
) AS CASELECT *
FROM #MyTable2;SELECT
MT.BatchID,
MT.RowID,
MAX(CASE MT.AttributeName WHEN 'WillField' THEN MT.AttributeValue END) AS WillField,
MAX(CASE MT.AttributeName WHEN 'Surname' THEN MT.AttributeValue END) AS Surname,
MAX(CASE MT.AttributeName WHEN 'FirstName' THEN MT.AttributeValue END) AS FirstName,
MAX(CASE MT.AttributeName WHEN 'CellPhone' THEN MT.AttributeValue END) AS CellPhone
FROM #MyTable2 AS MT
GROUP BY
MT.BatchID,
MT.RowID
ORDER BY
MT.BatchID,
MT.RowID;DROP TABLE #MyTable;
DROP TABLE #MyTable2;Thank you Steve 🙂🙂
You're very welcome!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply