August 29, 2015 at 8:55 am
Hello Need T-SQL help, If numeric value then I could get is using PIVOT, but string value is there, what is best way, How to query this, thanks for your helps, Thanks for your valuable time, Thanks so much.
the data and expected output as below. Please help,
ID,Col,seq,strdata
1, 'Col1', 1, 'I'
1, 'Col1', 2, 'Love'
1, 'Col1', 3, 'SQLservercentral'
1 'col2' 1, 'sql'
1 'col2' 2, 'server'
1 'col2' 3, 'centrl'
1 'col4' 1, 'Provides v'
1 'col4' 2, 'ery Helpfull'
1 'col6' 1, 'Ser'
1 'col6' 2, 'vice.'
2 'col1' 1, 'Thank You'
2 'col1' 2, ',So much.'
2 'col1' 3, 'For '
2 'col3' 1, 'All your'
2 'col3' 2, 'Helps'
2 'col4' 1, 'Appreciated,'
2 'col4' 2, 'Thanks. Thanks. Thanks.'
Output,
ID Col1Col2Col3 Col4Col5Col6
1 ILoveSQLserverCentrol sqlservercetrl ''provides very Helpfull ''Service
2 Thank you, So much For ''All your helps Appreciated,Thanks. Thanks. Thanks.
August 29, 2015 at 8:24 pm
Joe-420121 (8/29/2015)
Hello Need T-SQL help, If numeric value then I could get is using PIVOT, but string value is there, what is best way, How to query this, thanks for your helps, Thanks for your valuable time, Thanks so much.the data and expected output as below. Please help,
ID,Col,seq,strdata
1, 'Col1', 1, 'I'
1, 'Col1', 2, 'Love'
1, 'Col1', 3, 'SQLservercentral'
1 'col2' 1, 'sql'
1 'col2' 2, 'server'
1 'col2' 3, 'centrl'
1 'col4' 1, 'Provides v'
1 'col4' 2, 'ery Helpfull'
1 'col6' 1, 'Ser'
1 'col6' 2, 'vice.'
2 'col1' 1, 'Thank You'
2 'col1' 2, ',So much.'
2 'col1' 3, 'For '
2 'col3' 1, 'All your'
2 'col3' 2, 'Helps'
2 'col4' 1, 'Appreciated,'
2 'col4' 2, 'Thanks. Thanks. Thanks.'
Output,
ID Col1Col2Col3 Col4Col5Col6
1 ILoveSQLserverCentrol sqlservercetrl ''provides very Helpfull ''Service
2 Thank you, So much For ''All your helps Appreciated,Thanks. Thanks. Thanks.
You can still use the PIVOT operator. Or the old-school way of doing a pivot... either works. BTW, note how I put your data into code that inserts the test data into a table. You really should do this for us in the future.
DECLARE @test-2 TABLE (ID INTEGER, Col CHAR(4), seq INTEGER, strdata VARCHAR(50));
INSERT INTO @test-2 (ID, Col, seq, strdata)
VALUES (1, 'Col1', 1, 'I' ),
(1, 'Col1', 2, 'Love' ),
(1, 'Col1', 3, 'SQLservercentral' ),
(1, 'Col2', 1, 'sql' ),
(1, 'Col2', 2, 'server' ),
(1, 'Col2', 3, 'centrl' ),
(1, 'Col4', 1, 'Provides v' ),
(1, 'Col4', 2, 'ery Helpfull' ),
(1, 'Col6', 1, 'Ser' ),
(1, 'Col6', 2, 'vice.' ),
(2, 'Col1', 1, 'Thank You' ),
(2, 'Col1', 2, ',So much.' ),
(2, 'Col1', 3, 'For ' ),
(2, 'Col3', 1, 'All your' ),
(2, 'Col3', 2, 'Helps' ),
(2, 'Col4', 1, 'Appreciated,' ),
(2, 'Col4', 2, 'Thanks. Thanks. Thanks.' );
-- NEW SCHOOL: Use the PIVOT operator
WITH cte AS
(
-- for each ID/Col, get the strdata concatenated together
SELECT t1.ID,
t1.Col,
MAX(ca.x) x
FROM @test-2 t1
-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/ for how this FOR XML syntax works
CROSS APPLY (SELECT (SELECT strdata + ''
FROM @test-2 t2
WHERE t2.ID = t1.ID
AND t2.Col = t1.Col
ORDER BY t2.seq
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) ca(x)
GROUP BY t1.ID, t1.Col
)
SELECT ID, [Col1], [Col2], [Col3], [Col4], [Col5], [Col6]
FROM cte
PIVOT (MAX(x) FOR Col IN ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6])) AS PivotTable;
-- OLD SCHOOL:
WITH cte AS
(
-- for each ID/Col, get the strdata concatenated together
SELECT t1.ID,
t1.Col,
MAX(ca.x) x
FROM @test-2 t1
-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/ for how this FOR XML syntax works
CROSS APPLY (SELECT (SELECT strdata + ''
FROM @test-2 t2
WHERE t2.ID = t1.ID
AND t2.Col = t1.Col
ORDER BY t2.seq
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) ca(x)
GROUP BY t1.ID, t1.Col
)
SELECT ID
,Col1 = MAX(CASE WHEN Col = 'Col1' THEN x ELSE NULL END)
,Col2 = MAX(CASE WHEN Col = 'Col2' THEN x ELSE NULL END)
,Col3 = MAX(CASE WHEN Col = 'Col3' THEN x ELSE NULL END)
,Col4 = MAX(CASE WHEN Col = 'Col4' THEN x ELSE NULL END)
,Col5 = MAX(CASE WHEN Col = 'Col5' THEN x ELSE NULL END)
,Col6 = MAX(CASE WHEN Col = 'Col6' THEN x ELSE NULL END)
FROM cte
GROUP BY ID;
Both of these methods return the same result set:
ID Col1 Col2 Col3 Col4 Col5 Col6
-- --------------------- --------------- ------------- ----------------------------------- ---- --------
1 ILoveSQLservercentral sqlservercentrl NULL Provides very Helpfull NULL Service.
2 Thank You,So much.For NULL All yourHelps Appreciated,Thanks. Thanks. Thanks. NULL NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 30, 2015 at 3:26 am
Thanks Wayne for your help, Great help, My apologies for Not giving you the data in correct syntax, Sure to do it in the future, Thanks so much for your time,
September 1, 2015 at 7:32 pm
Hello Wayne, Very much appreciated for providing the query to two methods, it all worked out for me, Well understood, Thanks for your valuable time.
September 1, 2015 at 8:40 pm
Glad it was helpful for you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 19, 2015 at 11:39 am
Hello Wayne,
How to UNPIVOT the same with string Length of 5? I need get back to its original form (UNPIVOT) but with size of 5 characters. Could you help me using new school method? If possible please help me otherwise No worries if it takes too much of your time. I am so sorry for any inconvenience. Thanks for much for your support. Very Very much appreciated.
September 19, 2015 at 12:08 pm
Joe-420121 (9/19/2015)
Hello Wayne,How to UNPIVOT the same with string Length of 5? I need get back to its original form (UNPIVOT) but with size of 5 characters. Could you help me using new school method? If possible please help me otherwise No worries if it takes too much of your time. I am so sorry for any inconvenience. Thanks for much for your support. Very Very much appreciated.
can you please clarify what you require by "string length 5"
the code below
DECLARE @test-2 TABLE (ID INTEGER, Col CHAR(4), seq INTEGER, strdata VARCHAR(50));
INSERT INTO @test-2 (ID, Col, seq, strdata)
VALUES (1, 'Col1', 1, 'I' ),
(1, 'Col1', 2, 'Love' ),
(1, 'Col1', 3, 'SQLservercentral' ),
(1, 'Col2', 1, 'sql' ),
(1, 'Col2', 2, 'server' ),
(1, 'Col2', 3, 'centrl' ),
(1, 'Col4', 1, 'Provides v' ),
(1, 'Col4', 2, 'ery Helpfull' ),
(1, 'Col6', 1, 'Ser' ),
(1, 'Col6', 2, 'vice.' ),
(2, 'Col1', 1, 'Thank You' ),
(2, 'Col1', 2, ',So much.' ),
(2, 'Col1', 3, 'For ' ),
(2, 'Col3', 1, 'All your' ),
(2, 'Col3', 2, 'Helps' ),
(2, 'Col4', 1, 'Appreciated,' ),
(2, 'Col4', 2, 'Thanks. Thanks. Thanks.' );
-- NEW SCHOOL: Use the PIVOT operator
WITH cte AS
(
-- for each ID/Col, get the strdata concatenated together
SELECT t1.ID,
t1.Col,
MAX(ca.x) x
FROM @test-2 t1
-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/ for how this FOR XML syntax works
CROSS APPLY (SELECT (SELECT strdata + ''
FROM @test-2 t2
WHERE t2.ID = t1.ID
AND t2.Col = t1.Col
ORDER BY t2.seq
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) ca(x)
GROUP BY t1.ID, t1.Col
)
SELECT ID, [Col1], [Col2], [Col3], [Col4], [Col5], [Col6]
FROM cte
PIVOT (MAX(x) FOR Col IN ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6])) AS PivotTable;
produces this result....what do you now want to do?
+-----------------------------------------------------------------------------------------------------------------------+
¦ ID ¦ Col1 ¦ Col2 ¦ Col3 ¦ Col4 ¦ Col5 ¦ Col6 ¦
¦----+------------------------+-----------------+---------------+-------------------------------------+------+----------¦
¦ 1 ¦ ILoveSQLservercentral ¦ sqlservercentrl ¦ NULL ¦ Provides very Helpfull ¦ NULL ¦ Service. ¦
¦ 2 ¦ Thank You,So much.For ¦ NULL ¦ All yourHelps ¦ Appreciated,Thanks. Thanks. Thanks. ¦ NULL ¦ NULL ¦
+-----------------------------------------------------------------------------------------------------------------------+
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 19, 2015 at 4:04 pm
Hello Livingston, thanks for your reply, the table should look like as below data,
-----------------------------------------------------------------------------------------------------
(1, 'Col1', 1, 'ILove' ),
(1, 'Col1', 2, 'SQLse' ),
(1, 'Col1', 3, 'rverc'),
(1, 'Col1', 4, 'entra'),
(1, 'Col1', 5, 'l'),
(1, 'Col2', 1, 'sqlse' ),
(1, 'Col2', 2, 'rverc' ),
(1, 'Col2', 3, 'entrl' ),
(1, 'Col4', 1, 'Provi'),
(1, 'Col4', 2, 'des v'),
(1, 'Col4', 3, 'ery H'),
(1, 'Col4', 4, 'elpfu'),
(1, 'Col4', 5, 'll'),
(1, 'Col6', 1, 'Servi' ),
(1, 'Col6', 2, 'ce.'),
(2, 'Col1', 1, 'Thank'),
(2, 'Col1', 2, ' You,'),
(2, 'Col1', 3, 'So mu' ),
(2, 'Col1', 4, 'ch.Fo'),
(2, 'Col1', 5, 'r '),
(2, 'Col3', 1, 'All y'),
(2, 'Col3', 2, 'ourHe' ),
(2, 'Col3', 3, 'lps'),
(2, 'Col4', 1, 'Appre'),
(2, 'Col4', 2, 'ciated'),
(2, 'Col4', 3, ',Than'),
(2, 'Col4', 4, 'ks. T'),
(2, 'Col4', 5, 'hanks'),
(2, 'Col4', 6, '. Tha'),
(2, 'Col4', 7, 'nks.');
--------------------------------------------------------------------------------------------
splitting the data with 5 characters in length. Hope this helps.
Thanks for your time looking into this. appreciated so much.
September 22, 2015 at 5:54 am
Could someone help me with this query request? Thanks so much for your help.
September 22, 2015 at 3:49 pm
Joe-420121 (9/19/2015)
Hello Livingston, thanks for your reply, the table should look like as below data,-----------------------------------------------------------------------------------------------------
(1, 'Col1', 1, 'ILove' ),
(1, 'Col1', 2, 'SQLse' ),
(1, 'Col1', 3, 'rverc'),
(1, 'Col1', 4, 'entra'),
(1, 'Col1', 5, 'l'),
(1, 'Col2', 1, 'sqlse' ),
(1, 'Col2', 2, 'rverc' ),
(1, 'Col2', 3, 'entrl' ),
(1, 'Col4', 1, 'Provi'),
(1, 'Col4', 2, 'des v'),
(1, 'Col4', 3, 'ery H'),
(1, 'Col4', 4, 'elpfu'),
(1, 'Col4', 5, 'll'),
(1, 'Col6', 1, 'Servi' ),
(1, 'Col6', 2, 'ce.'),
(2, 'Col1', 1, 'Thank'),
(2, 'Col1', 2, ' You,'),
(2, 'Col1', 3, 'So mu' ),
(2, 'Col1', 4, 'ch.Fo'),
(2, 'Col1', 5, 'r '),
(2, 'Col3', 1, 'All y'),
(2, 'Col3', 2, 'ourHe' ),
(2, 'Col3', 3, 'lps'),
(2, 'Col4', 1, 'Appre'),
(2, 'Col4', 2, 'ciated'),
(2, 'Col4', 3, ',Than'),
(2, 'Col4', 4, 'ks. T'),
(2, 'Col4', 5, 'hanks'),
(2, 'Col4', 6, '. Tha'),
(2, 'Col4', 7, 'nks.');
--------------------------------------------------------------------------------------------
splitting the data with 5 characters in length. Hope this helps.
Thanks for your time looking into this. appreciated so much.
What are you going to be doing with this output? (It looks like you might be using it to create a SQL statement... by doing this, you are priming yourself for a SQL Injection attack (see my blog post[/url] that explains this.
To get just the data, by adding on to the previous query we get this:
DECLARE @test-2 TABLE (ID INTEGER, Col CHAR(4), seq INTEGER, strdata VARCHAR(50));
-- specify how many characters to get at a time.
DECLARE @ColumnLength DECIMAL(10,1) = 5;
INSERT INTO @test-2 (ID, Col, seq, strdata)
VALUES (1, 'Col1', 1, 'I' ),
(1, 'Col1', 2, 'Love' ),
(1, 'Col1', 3, 'SQLservercentral' ),
(1, 'Col2', 1, 'sql' ),
(1, 'Col2', 2, 'server' ),
(1, 'Col2', 3, 'centrl' ),
(1, 'Col4', 1, 'Provides v' ),
(1, 'Col4', 2, 'ery Helpfull' ),
(1, 'Col6', 1, 'Ser' ),
(1, 'Col6', 2, 'vice.' ),
(2, 'Col1', 1, 'Thank You' ),
(2, 'Col1', 2, ',So much.' ),
(2, 'Col1', 3, 'For ' ),
(2, 'Col3', 1, 'All your' ),
(2, 'Col3', 2, 'Helps' ),
(2, 'Col4', 1, 'Appreciated,' ),
(2, 'Col4', 2, 'Thanks. Thanks. Thanks.' );
-- NEW SCHOOL: Use the PIVOT operator
WITH cte AS
(
-- for each ID/Col, get the strdata concatenated together
SELECT t1.ID,
t1.Col,
MAX(ca.x) x
FROM @test-2 t1
-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/ for how this FOR XML syntax works
CROSS APPLY (SELECT (SELECT strdata + ''
FROM @test-2 t2
WHERE t2.ID = t1.ID
AND t2.Col = t1.Col
ORDER BY t2.seq
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
) ca(x)
GROUP BY t1.ID, t1.Col
)
-- the next 4 CTEs build a dynamic tally / numbers table from 1 - 1,000,000
, Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), Thousands (N) AS (SELECT t1.N FROM Tens t1, Tens t2, Tens t3
), Millions (N) AS (SELECT t1.N FROM Thousands t1, Thousands t2
), Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions
)
, cte2 AS (
SELECT ID, [Col1], [Col2], [Col3], [Col4], [Col5], [Col6]
FROM cte
PIVOT (MAX(x) FOR Col IN ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6])) AS PivotTable
)
SELECT t1.ID,
ca1.SourceColumn,
ca2.N,
ca2.ColumnValue
FROM cte2 t1
-- unpivot the rows
CROSS APPLY (VALUES ('Col1', Col1, ISNULL(CONVERT(INTEGER, CEILING(LEN(Col1) / @ColumnLength)), 0)),
('Col2', Col2, ISNULL(CONVERT(INTEGER, CEILING(LEN(Col2) / @ColumnLength)), 0)),
('Col3', Col3, ISNULL(CONVERT(INTEGER, CEILING(LEN(Col3) / @ColumnLength)), 0)),
('Col4', Col4, ISNULL(CONVERT(INTEGER, CEILING(LEN(Col4) / @ColumnLength)), 0)),
('Col5', Col5, ISNULL(CONVERT(INTEGER, CEILING(LEN(Col5) / @ColumnLength)), 0)),
('Col6', Col6, ISNULL(CONVERT(INTEGER, CEILING(LEN(Col6) / @ColumnLength)), 0))
)ca1 (SourceColumn, ColumnValue, RowsToMake)
-- now get the specified number of characters
CROSS APPLY (SELECT TOP (ca1.RowsToMake) N, SUBSTRING(ca1.ColumnValue, (N*@ColumnLength)-(@ColumnLength-1), @ColumnLength)
FROM Tally) ca2(N, ColumnValue)
ORDER BY t1.ID, ca1.SourceColumn, ca2.N;
Which returns the following results:
ID SourceColumn N ColumnValue
----------- ------------ -------------------- -------------
1 Col1 1 ILove
1 Col1 2 SQLse
1 Col1 3 rverc
1 Col1 4 entra
1 Col1 5 l
1 Col2 1 sqlse
1 Col2 2 rverc
1 Col2 3 entrl
1 Col4 1 Provi
1 Col4 2 des v
1 Col4 3 ery H
1 Col4 4 elpfu
1 Col4 5 ll
1 Col6 1 Servi
1 Col6 2 ce.
2 Col1 1 Thank
2 Col1 2 You,
2 Col1 3 So mu
2 Col1 4 ch.Fo
2 Col1 5 r
2 Col3 1 All y
2 Col3 2 ourHe
2 Col3 3 lps
2 Col4 1 Appre
2 Col4 2 ciate
2 Col4 3 d,Tha
2 Col4 4 nks.
2 Col4 5 Thank
2 Col4 6 s. Th
2 Col4 7 anks.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 22, 2015 at 11:43 pm
Hello Wayne,
Thank you so much for your time & efforts. Very much appreciated. I could never get to this level of complexity.
Very much impressed with your technical ability and patience to help this out for me. Very awesome & smart work.
I do have this situation in my work, & take care of your concerns related to SQL injection, Thanks so much for article.
September 23, 2015 at 7:48 am
Joe-420121 (9/22/2015)
Hello Wayne,Thank you so much for your time & efforts. Very much appreciated. I could never get to this level of complexity.
Very much impressed with your technical ability and patience to help this out for me. Very awesome & smart work.
I do have this situation in my work, & take care of your concerns related to SQL injection, Thanks so much for article.
sure you can... you just have to APPLY yourself (pun intended). Keep on learning, and you'll get there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply